Strip a letter from the end of a numeric field

[Olivepetunia]

When I wrote simple macros using Lotus 1-2-3 (fifteen years ago) I could easily perform simple repetitive activities. For example I have a list of several thousand rows with amounts that have a letter or symbol appended to the end. In lotus I could {edit}{Backspace}~{Down} and each cell would have the letter removed and the value entered in the active cell. When I try this with Visual Basic in Excel, the macro enters the value that is in the cell used to write the macro, i.e., when executed it enters the same value in every cell regardless of what the actual contents are. For example, using the following ten cell contents in Excel
00000072K
00000755}
00000141O
00000802N
00000921M
00001520}
00000110L
00001981}
00001460N
00000235Q

The resulting macro for the first five looks like this:
Sub Macro1()
'
' Macro1 Macro
' strip
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.FormulaR1C1 = "0.72"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "7.55"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "1.41"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "8.02"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "9.21"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

When I run this macro twice, i.e., once for each 5 cells the results (the same results twice!!) are in the left column… The desired results are in the right column.
Should be
0.72 0.72
7.55 7.55
1.41 1.41
8.02 8.02
9.21 9.21
0.72 15.2
7.55 1.1
1.41 19.81
8.02 14.6
9.21 2.35

What am I doing wrong? How do I get the results I want?

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[Special-K]

Cant you just use a Helper column, all your data is 9 characters in length
B1=LEFT(A1,8)*1
should strip the last character and convert it to numeric

[Olivepetunia]

Thank you for the suggestion.
I think I left out one relevant fact that I probably should have mentioned: I created this using the macro recorder. When I wrote macros in Lotus I was able to teach myself as the language was quasi-English. I have tried to learn Visual Basic several times but with almost no success. The macro recorder works nicely only if you are performing EXACTLY the same task as recorded.
The result you suggested works very nicely but only one cell at a time. Is there a simple way to get this to go down and repeat for the entire length of my column and then stop?

[JosephP]

TMShucks' code will work on every cell in the selected range so if you select all your cells first it will adjust all of them ;-)

[TMS]

@JP: Thanks

@OlivePetunia: Interestingly, I don't get the Offset.Select statements that you get, I get specific cell selections. Clearly, as well as removing the last character, you add a decimal point.

Some variations on a theme:

Please Login or Register  to view this content.

Typically, recording a macro will repeat exactly what you did when you recorded it and, more often than not, act on the cells you originally changed. To make it more general, you need to tweak it.

I'm sure that even with Lotus 1-2-3 you would have needed to take the basic recorded code and make it loop around to go through all the cells. And editing a value is pretty much the same as typing it so you would, I believe, have needed some action other than entering an amended value.

Anyway, the forum is a great place to learn.

Regards, TMS