5
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Is there a way I can attach a spreadsheet? I'm new here so please let me know if I'm not doing it right or something. \$\endgroup\$ – msim Feb 10 '15 at 21:03
  • \$\begingroup\$ ok I tried to demonstrate this. The code I am using is fully up there, there's nothing else going on. It's just that the loops need to happen for 500 more columns so I haven't written those yet, I thought I'd check here first before doing that to see if I don't have to write so much. Thanks. \$\endgroup\$ – msim Feb 10 '15 at 21:14
  • \$\begingroup\$ Okay. So those hardcoded variable values represent your headers. Got it. Thanks. \$\endgroup\$ – RubberDuck Feb 10 '15 at 21:17
3
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ You are missing a quote in your match formula part after $ZA$2 \$\endgroup\$ – Chrismas007 Feb 11 '15 at 3:27
  • \$\begingroup\$ well...thank you very much for your time, but i knew i could do this. many people are going to be using what I'm building and I'd rather it was code than a spread with preloaded formulas that they can easily mess up by not being careful. I want to get better at writing VBA so I will google the things that you listed that you wouldn't talk about. I've done a smaller version of this and it takes a fraction of a second to find all the data, so speed of the macro isn't really my concern. \$\endgroup\$ – msim Feb 11 '15 at 5:43
  • \$\begingroup\$ @msim speed may not be a concern right now, but it will be later when it takes ages to loop through every cell in every worksheet. I'll tell you what though, since you're unhappy with my suggestion, I'll come back and some more to my answer later. \$\endgroup\$ – RubberDuck Feb 11 '15 at 9:58
  • 1
    \$\begingroup\$ What about an array? Would that help? Like an array for my TEXT variables, array for my match variables, and then say for each variable in the array, do this, and when the variable is supposed to be in there, write the name of the array? I don't know much of anything about arrays, or how to write what I just explained, but it makes sense in my head. \$\endgroup\$ – msim Feb 11 '15 at 22:58
  • \$\begingroup\$ Yes, only I would recommend For Each cell In Range("B1:whatever1"). Sorry. I've not had time to properly review your code again. I will though. Honest. \$\endgroup\$ – RubberDuck Feb 11 '15 at 23:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.