Changing from 1 Letter to desired Number

[CooKieLord]

Hi, I hope someone can help me out before I have to go, but I need a Macro in Microsoft Excel that can change letters into a number I want.

i.e. HM6GP

I will need to change the H into 1, the M into 7, 6 stays the same, G into 9 and P into 8.

So far I have

code:

Sub Convert Dim Letter as Range end Sub

Problem is it's been a while since I last used this. I really need a macro cause this work is over 100 pages.

I hope I'm being slear enough for you to understand.
Thanks to those who take the time to read this, and even more to those who reply!

[NikG]

Here's a test function I wrote for the coversion. Just pass the text into it and it should result in what you need (if I understood correctly).

code:

Function Convert(Text As String) As String     Dim Text2 As String     Dim i As Integer         Text2 = ""     For i = 1 To Len(Text)         If IsNumeric(Mid$(Text, i, 1)) Then         Text2 = Text2 + Mid$(Text, i, 1)         Else             If Mid$(Text, i, 1) = "H" Then                 Text2 = Text2 + "1"             ElseIf Mid$(Text, i, 1) = "M" Then                 Text2 = Text2 + "7"             ElseIf Mid$(Text, i, 1) = "G" Then                 Text2 = Text2 + "9"             ElseIf Mid$(Text, i, 1) = "P" Then                 Text2 = Text2 + "8"             End If         End If     Next i     Convert = Text2 End Function

Hope this helped.

[CooKieLord]

I think it might help, but how would it work as a Macro? I haven't gotten it to run yet, I'm such a noob D:

[NikG]

Well as part of your macro, put the value of whatever range you are looking at through the function above and replace it with the result.

In most of my macros, I usually just create a loop that keeps running until it runs of of data (i.e. Do While Range("A" & cntr).value <> "")

[CooKieLord]

Well if I recall correctly, VB needs

code:

Sub (blabla) Dim whatever as long etc etc End Sub

And just sticking in your function doesn't work, I really can't use macros.
I really appreciate you helping a hopeless person like me. Thanks a bunch.

[NikG]

Ok, here's how I would make this macro. You'll have to change it around to suit what you actually need. You'll also need that Convert function I wrote earlier.

I'm gonna make a few assumptions first:

- Those pieces of text you are converting are all in the same column (i.e. column A)
- The number of rows with the data is variable (i.e. it will change everytime you run this macro)
- IMPORTANT: there are NO spaces in between rows, particularly in column A (you'll understand the reason for this after seeing my code.)

code:

Sub MACRO_CONVERT()     Dim line As Integer     line = 1     Do While Range("A" & line).Value <> ""         Range("A" & line).Value = Convert(Range("A" & line).Value)         line = line + 1     Loop End Sub

To run it, you'd just go to Tools>Macro>Macros (or Alt+F8) and run MACRO_CONVERT. Basically, this goes through every row of column A converting the value, and stopping only when a row of column A is blank. That's why you can't have any spaces in between. If you do, you'll have to find some other way to make the loop run and stop properly.