 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2006Excel MAcro to insert text from one column to other
Hello, I have a worksheet in which my data is arranged as per temp. I have Temp in one column and the corresponding value in the other. I need to inser For eg. I have temp 25C in two adjacent columns i have to make it Temp: 25C and remove the 25C from that column. Can I get some help with the Excel Macro for the same. Dhawal... Using a third, helper column such as column C, you could put this formula to CONCATENATE the two adjacent cells into one......... =A1&": "&B1 Then you could highlight column C and Do Copy > Pastespecial > Values to get rid of the formulas.....then you could delete your old columns A and B if you wish......... hth Vaya con Dios, Chuck, CABGx3 > Hello, > [quoted text clipped - 13 lines] > > Dhawal... Thank you. But I have a whole bunch of columns to take care of, about 100 of them and they are separate.. Is it efficient to do it this way. > Using a third, helper column such as column C, you could put this formula to > CONCATENATE the two adjacent cells into one......... [quoted text clipped - 26 lines] > > > > Dhawal... Dhawal This macro has no error checking - Select the "Temp"'s and run the macro - it will delete the entire column with the actual temperatures. Sub CONCAT() For Each C In Selection C.Value = C & ": " & C.Offset(0, 1) Next ActiveCell.Offset(0, 1).Select Selection.EntireColumn.Delete End Sub Press Alt + F11 to open the VBE, choose Insert Module and copy the macro. Return to the workbook select the Temps Press Alt + F8 choose the macro and run it. You will have to repeat this for each of your columns of data. Regards Peter > Hello, > [quoted text clipped - 13 lines] > > Dhawal... Hello, I really apprecite the help. The macro solves my problem. But what if I have a different data in the same column below 'temp'? Thank you, Dhawal > Dhawal > [quoted text clipped - 35 lines] > > > > Dhawal... Dhawal Well, the macro will only work on the selection. Different data below will not be affected; so do not select the entire column. However, if you have selected data in column A then the entire column B will be deleted. I'll have another look and maybe I can delete just the data in column B if that would be better for you. Regards Peter > Hello, > [quoted text clipped - 44 lines] > > > > > > Dhawal... I've rewritten the macro so that the offset column is just cleared rather than deleted, I'm sure that it is safer this way. Sub CONCAT() ' Only select the column that you want to ' join the data - not both columns. For Each c In Selection c.Value = c & ": " & c.Offset(0, 1) c.Offset(0, 1).Value = "" Next End Sub Hope this answers your query. Regards Peter > Dhawal > [quoted text clipped - 56 lines] > > > > > > > > Dhawal... Dhawal Better still this will only work if the selected cells contain "Temp" and will ignore other entries. Sub CONCAT() ' Only select the column that you want to ' join the data - not both columns. For Each c In Selection If Trim(LCase(c)) = "temp" Then c.Value = c & ": " & c.Offset(0, 1) c.Offset(0, 1).Value = "" End If Next End Sub Sorry for the delay - I went to bed. Peter > Hello, > [quoted text clipped - 44 lines] > > > > > > Dhawal... Thank you Peter, That absolute solves my problem. I really appreciate your help. Dhawal > Dhawal > [quoted text clipped - 65 lines] > > > > > > > > Dhawal...
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.