PDA

View Full Version : [SOLVED] VBA to replace numerical data with Alphnumerical data.



estatefinds
03-31-2016, 05:38 AM
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
03-31-2016, 10:44 AM
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
03-31-2016, 12:50 PM
Did that work and do what you want Dennis?

estatefinds
03-31-2016, 03:30 PM
Running it now, thank you!

PAB
03-31-2016, 03:35 PM
You're welcome.

estatefinds
03-31-2016, 03:57 PM
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
03-31-2016, 04:01 PM
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
03-31-2016, 04:05 PM
Over 400,000 can this be done in macros?

PAB
03-31-2016, 04:06 PM
Give me 5 minutes and I will post a Macro that should do what you want!

estatefinds
03-31-2016, 04:07 PM
Ok great! Thank you!

PAB
03-31-2016, 04:20 PM
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
03-31-2016, 06:05 PM
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
04-01-2016, 12:08 AM
You're welcome, thanks for the feedback.

PAB
04-01-2016, 04:06 AM
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.