Quicker way to go through 550 variables with very similar code

Question

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, 0)
    Next i
        Exit For
    End If
Next v

And repeats and repeats...500+ times. Is there a better way to do this? Please teach me if so.

EDIT: Here's a better explanation of the function, it's in a spreadsheet so . The MasterTab looks like this

sLN iA  pLN iC  iB  aLN
100                 
101                 
102                 
103                 
104                 
105                 
106                 
107                 
108                 
109                 
110                 

those are the headings. Matching heading can be found on five other tabs: B, E, L, I, and T.

The code above does a vlookup match to search all 5 tabs for a column with the right name (as defined by the mf_x_TEXT variable). Then the spreadsheet looks like this:

sLN iA  pLN iC  iB  aLN
100 4444    a   etc etc etc
101 4444    b           
102 4746    c           
103 8844    d           
104 8383    e           
105 3456    f           
106 8836    g           
107 2342    h           
108 8888    i           
109 1111    j           
110 1123    k           

like I said, it works, but there are 500 more columns where that came from and I'm just trying to shorten my work so I don't have to copy/paste/click so much. Hope that helps, let me know if I can provide anything else.

Answer 1

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.