VBA to replace numerical data with Alphnumerical data.

[estatefinds]

So I need a Macro to simply replace the numerical data that is in column E with the alpha numeric associated with each number in the combination in column E.
in column A are the numbers 1 to 35 these will never change.
the alpha numeric in column B will change on occasion so VBA needs to work with new data.
in column B there are alpha numeric that are associated with the numbers in column A.
in column E there is a 5 number combination that made up of the numbers 1 to 35 and delimited.
these combinations are then unmerged to the columns G H I J K. the columns M N O P Q will be where the alpha numeric replacing the numbers will be placed.

if any questions please ask, I am attaching example.
Thank you!

[PAB]

Hi Dennis,

I think the data values you have in cells P2 (should be value C5), Q5 (should be value E16, there is NO E9), P10 (should be value C2) are wrong.

Put this formula in cell M2 and copy it across and down.

=VLOOKUP(G2,$A$2:$B$35,2)

I hope this helps!

[PAB]

Did that work and do what you want Dennis?

[estatefinds]

Running it now, thank you!

[PAB]

You're welcome.

[estatefinds]

Ok so I added my data and it takes a long time it's still running, is there anyway this can be done in macros?

[PAB]

OK, so I added my data and it takes a long time, it's still running, is there anyway this can be done in macros?

I can't understand why it is taking a long time to run because they are only formulas?
How much data are we talking about?

[estatefinds]

Over 400,000 can this be done in macros?

[PAB]

Give me 5 minutes and I will post a Macro that should do what you want!

[estatefinds]

Ok great! Thank you!

[PAB]

OK Dennis, give this a go.

Option Explicit
Option Base 1
Sub Replace_Num_With_Alpha()
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
    Range("M2:Q" & Range("E" & Rows.Count).End(xlUp).Row).ClearContents
    Range("M2:Q" & Range("E" & Rows.Count).End(xlUp).Row).Formula = _
        "=VLOOKUP(G2,$A$2:$B$35,2)"
    Range("M2:Q" & Range("E" & Rows.Count).End(xlUp).Row).Value = _
        Range("M2:Q" & Range("E" & Rows.Count).End(xlUp).Row).Value
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

Please let me know how you get on!

[estatefinds]

ok i ran it and the number 35 is listed as E16 and it supposed to be E9.
also this data will change in column B but only after I run this with the original data in column B.
can this be fixed?

shouldnt it be reading what is in the data column A and B as it is displayed?

I just figured it out the code had to be to row 36 now it works like it should.

Thank you for the code it works Great!!! and very fast Thank you!!!!

[PAB]

You're welcome, thanks for the feedback.

[PAB]

Hi Dennis,

...also, this data will change in column B, but only after I run this with the original data in column B.
Can this be fixed?

If the data is going to be dynamic in columns A & B, either grow or shrink once you have run the code, then you will need to add a dynamic range variable to the code and use that.