 |
 | 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 / Programming / April 2008Macro
I receive, daily, a spreadsheet with a lot of data. In one column, all the cells contain numbers and text and I want to change this to reflect the numbers only (which must represent values). The text portion is always the same in all of the cells. I have recorded a macro which places the cursor in the first cell, edits the cell by backspacing 6 times (which removes the text portion), moves to the next cell and does the same. However the number of rows differ every day. How do I get the macro to execute as far as the extent of the data? Can anyone help? Hi, If you always wnat to remove the first 6 characters then this would strip the first 6 characters from the used range in column and leave whats left as a number Right click the worksheet, view code and paste it in Sub stance() Dim myrange As Range Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set myrange = Range("A1:A" & Lastrow) For Each c In myrange c.Value = (Mid(c.Value, 7)) + 0 Next End Sub Mike > I receive, daily, a spreadsheet with a lot of data. In one column, all the > cells contain numbers and text and I want to change this to reflect the [quoted text clipped - 4 lines] > rows differ every day. How do I get the macro to execute as far as the extent > of the data? Can anyone help? slightly adapted to cut off last 6 characters Sub stance() Dim myrange As Range Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set myrange = Range("A1:A" & Lastrow) For Each c In myrange c.Value = Left(c.Value, Len(c.Value - 6)) Next End Sub  SignatureRegards Jean-Yves Tfelt Europe > Hi, > [quoted text clipped - 22 lines] > > rows differ every day. How do I get the macro to execute as far as the extent > > of the data? Can anyone help? I think you may have meant c.Value = Left(c.Value, Len(c.Value) - 6) A lack of information from the OP makes it pure guesswork whether it was the 6 rightmost or 6 leftmost characters that are to be removed. Mike > slightly adapted to cut off last 6 characters > Sub stance() [quoted text clipped - 32 lines] > > > rows differ every day. How do I get the macro to execute as far as the extent > > > of the data? Can anyone help? I assumed that by saying I backspaced 6 times to remove text inferred rightmost but I'll keep that in mind. > I think you may have meant > [quoted text clipped - 41 lines] > > > > rows differ every day. How do I get the macro to execute as far as the extent > > > > of the data? Can anyone help? Hi Mike, Yes indeed, SignatureRegards Jean-Yves Tfelt Europe > I think you may have meant > [quoted text clipped - 41 lines] > > > > rows differ every day. How do I get the macro to execute as far as the extent > > > > of the data? Can anyone help? Hi Mike, thanks for this. Actually I need to remove the last 6 characters which are always the same. Basically the cell looks like this: 15.60space%/MWh. The %is actually a euro sign. The values vary but not the text. Are you saying I should paste your solution below into the spreadsheet? Leon > Hi, > [quoted text clipped - 22 lines] > > rows differ every day. How do I get the macro to execute as far as the extent > > of the data? Can anyone help? Then use the line supplied by the other responder but be sure to correct the syntax to c.Value = Left(c.Value, Len(c.Value) - 6) and unless you add zero to it it will be formatted as text still so the correct line is c.Value = Left(c.Value, Len(c.Value) - 6) +0 Mike > Hi Mike, thanks for this. Actually I need to remove the last 6 characters > which are always the same. Basically the cell looks like this: [quoted text clipped - 28 lines] > > > rows differ every day. How do I get the macro to execute as far as the extent > > > of the data? Can anyone help? OK, thanks, BUT where do I store this code. My macro usage runs to recording and occassionally stepping into to make corrections. How do I go about storing your list of instructions - (with the other amendments) > Then use the line supplied by the other responder but be sure to correct the > syntax [quoted text clipped - 41 lines] > > > > rows differ every day. How do I get the macro to execute as far as the extent > > > > of the data? Can anyone help? Hi Leon, See my first repply You can as well use a formula in the next column & fill down =LEFT(A1,LEN(A1)-4) where 4 is the number or character to remove To use the code from Mark,( Are you saying I should paste your solution below into the spreadsheet?) You need to copy it to a standard VBA module : Press aLT + F11 (to open VBA Editor), then via Menu "Insert"select Module then paste. To run it (save before your work, there is NO Undo comand aferr a macro run !) press F5 in the VBA editor or In the Excel window, goto Menu Tools/Macro ../Macros You should see the name of the routine from Mark called "stance". Select and Run. HTH SignatureRegards Jean-Yves Tfelt Europe > Hi Mike, thanks for this. Actually I need to remove the last 6 characters > which are always the same. Basically the cell looks like this: [quoted text clipped - 28 lines] > > > rows differ every day. How do I get the macro to execute as far as the extent > > > of the data? Can anyone help? Merci beaucoup Jean-Yves. I will try that and see how it goes. > Hi Leon, > [quoted text clipped - 47 lines] > > > > rows differ every day. How do I get the macro to execute as far as the extent > > > > of the data? Can anyone help? |
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.