Quicker way to go through 550 variables with very similar code

I'm hoping to figure out a faster way to write this before I resign myself to copying and pasting 500 times. I have code here that needs to do tons of vlookup matches. The only thing that changes are two variables each time.

First part is just establishing variables and a count of the rows I need to loop through for each thing:

Sub GetInfo()
    Dim v As Long, vWSs As Variant, Mrange As Range, Vrange As Range
    Dim wsMaster As Worksheet: Set wsMaster = Workbooks("LBImportMacroTemplate.xlsm").Worksheets("MasterTab")
    Dim mf_iA_TEXT As String: mf_iA_TEXT = "iA"
    Dim mf_pLN_TEXT As String: mf_pLN_TEXT = "pLN"
    'ET CETERA, MANY MORE STRING VARIABLES LIKE THIS
    vWSs = Array("B", "E", "L", "I", "T")
    With Workbooks("LBImportMacroTemplate.xlsm")
        Set Mrange = Nothing: Set Vrange = Nothing
'
With ActiveSheet
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    MsgBox lastrow
End With

Now, the main code:

'iA
For v = LBound(vWSs) To UBound(vWSs)
    If CBool(Application.CountIf(.Sheets(vWSs(v)).Range("A2:ZA2"), mf_iA_TEXT)) Then
        Set Mrange = .Sheets(vWSs(v)).Range("A2:ZA2")
        Set Vrange = .Sheets(vWSs(v)).Range("A:ZA")
        mf_iA = Application.Match(mf_iA_TEXT, Mrange, 0)
    For i = 2 To lastrow
            wsMaster.Cells(i, 2) = Application.VLookup(wsMaster.Cells(i, 1), Vrange, mf_iA, 0)
    Next i
        Exit For
    End If
Next v

Then this part repeats again, with two different variables (the mf_x and mf_x_TEXT are the constant naming things but something else will be in place of the x every time.

```
'pLN
For v = LBound(vWSs) To UBound(vWSs)
If CBool(Application.CountIf(.Sheets(vWSs(v)).Range("A2:ZA2"), mf_pLN_TEXT)) Then
Set Mrange = .Sheets(vWSs(v)).Range("A2:ZA2")
Set Vrange = .Sheets(vWSs(v)).Range("A:ZA")
mf_pLN = Application.Match(mf_pLN_TEXT, Mrange, 0)
For i = 2 To lastrow
wsMaster.Cells(i, 3) = Application.VLookup(wsMaster.Cells(i, 1), Vrange, mf_pLN

This is an odd code review for me. I could talk about giving your variables meaningful names, proper indentation, avoiding activate and select, extracting functions/subs, looping over ranges using a For Each, etc. , but I'm not going to talk about any of that. Because you don't need code to do this at all.

With some patience, you should be able to craft a native excel formula that does all of this. It will be less readable than code, but it will also be exponentially faster.

Let's start here:

wsMaster.Cells(i, 3) = Application.VLookup(wsMaster.Cells(i, 1), Vrange, mf_pLN, 0)

and translate this into a formula.

=VLOOKUP("[MasterTab]!$A2","[Sheet2]!A:ZA",mf_Pln,FALSE)

We obviously can't use a variable there, so let's take a look at how we get that value.

mf_pLN = Application.Match(mf_pLN_TEXT, Mrange, 0)

Okay, so let's nest that into our function.

=VLOOKUP("[MasterTab]!$A2","[Sheet2]!A:ZA",Match(A$1, "[Sheet2]!$A$2:$ZA$2", 0),FALSE)

Take careful note of the dollar signs. They make the reference absolute, so the reference cell doesn't change as the formula gets copied into new cells. Those without a $ are relative and will "move with" the cells.

Now, just nest this inside of your COUNTIF and you have a formula ready to go into your worksheet. Fill right, then down.

Of course, this works on a single specified worksheet. You'll still need to loop through all of the worksheets to actually build your final formula. Replacing the target sheet name accordingly.

It's going to be nested deep, but a considerably faster solution using formulas versus VBA.

StackExchange Code Review Q#80200, answer score: 3