-----------------------------------

CooKieLord

Fri Apr 07, 2006 9:24 am


Changing from 1 Letter to desired Number

-----------------------------------

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

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

Fri Apr 07, 2006 10:38 am



-----------------------------------

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).
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

Mon Apr 10, 2006 7:26 am



-----------------------------------

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

Mon Apr 10, 2006 11:11 am



-----------------------------------

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

Thu Apr 13, 2006 9:41 am



-----------------------------------

Well if I recall correctly, VB needs
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

Thu Apr 13, 2006 11:13 am



-----------------------------------

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.)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.