need help constructing a vba macro by using a key chart to reanslate number combin to

[estatefinds]

I have data in columns B and C. The data in column B is the number 1-35, the data in column C is alpha numeric. this is the key chart.
I have data in column D which are numerical combinations.
I need a macro in which I press ALT F8 and it looks at all the data in column D and uses the key chart to out put the alphanumerics for the numeric combinations into columns H:L.



also the data in the column C will change as I enter updated data.


The data will get larger so the macro needs to be AS Long.
I had attach example as well.

Thank you in advnace for your help!!!

[mikerickson]

You don't need a macro.
Put =VLOOKUP(MID(SUBSTITUTE($D38,"-",REPT(" ",20)),1+(20*(COLUMN(A38)-1)),20)+0,$B:$C,2,FALSE) in H1 and drag down and right.

If you want a macro:


Sub Test()
With Sheet1
With Range(.Cells(38, 2), .Cells(Rows.Count, 2).End(xlUp))
With .Offset(0, 6).Resize(.Rows.Count, 5)
.FormulaR1C1 = "=VLOOKUP(MID(SUBSTITUTE(RC4,""-"",REPT("" "",20)),1+(20*(COLUMNS(C1:C[-7])-1)),20)+0,C2:C3,2,FALSE)"
End With
End With
End With
End Sub

[estatefinds]

How do I get it to look at all data in column D? It stops at row 72.

[estatefinds]

the code works great!!! just need it to be able to read all data in column D
As I will be adding more later much more. Thank you

[estatefinds]

How do I get it to read data in column D up to over 500,000 rows? using the macro

[Paul_Hossler]

Try something like this




Option Explicit
Sub Translate()
Dim X As Variant, V As Variant
Dim rStart As Range, rEnd As Range, rCell As Range
Dim i As Long

Set rStart = Range("C38")
Set rEnd = rStart.End(xlDown)

ReDim X(1 To rEnd.Row - rStart.Row + 1)
X = Application.WorksheetFunction.Transpose(Range(rStart, rEnd).Value)

Set rStart = Range("D38")
Set rEnd = rStart.End(xlDown)

Application.ScreenUpdating = False
For Each rCell In Range(rStart, rEnd).Cells
V = Split(rCell.Value, "-")
For i = LBound(V) To UBound(V)
rCell.Offset(0, 4 + i).Value = X(V(i))
Next i
Next

Application.ScreenUpdating = True
End Sub

[estatefinds]

This code worked Great!!! This is exactly what I needed. Thank you Very much!

[mikerickson]

My mistake, I got the column number wrong


Sub Test()
With Sheet1
With Range(.Cells(38, 4), .Cells(Rows.Count, 4).End(xlUp))
With .Offset(0, 6).Resize(.Rows.Count, 5)
.FormulaR1C1 = "=VLOOKUP(MID(SUBSTITUTE(RC4,""-"",REPT("" "",20)),1+(20*(COLUMNS(C1:C[" & (1-.Column) & "])-1)),20)+0,C2:C3,2,FALSE)"
End With
End With
End With
End Sub

[estatefinds]

No worries, Thank you very much!!!