Application.Match only working on Lookup tab active

Question

I'm using Userforms and have 2 main worksheets. 1 (shE) collates the information selected, and 2 (shL) is where all available options are stored - these are my lookups.

shL has a list of teams on Row 2, every 4 columns, and on Row 3, a list of attributes for each team. So the structure is AB2 = Team1, AF2 = Team2, AJ2 = Team3. AB3 = Attribute1, AC3 = Attribute2, AD3 = Attribute3, AE3 = Attribute4, AF3 = Attribute1 etc..

Dim shE, shL as Worksheet
Dim FoundTeam as range
Dim c, cc, y, mtc as long
    Set shE = Sheets("EnteredData")
    Set shL = Sheets("Lookups")
    Set FoundTeam = shL.Range("AB2:BX2").Find(what:=shE.Range("D10"))
    c = FoundTeam.Column
    cc = c + 3
    y = shL.Cells(shL.Rows.Count, c).End(xlUp).Row
    ' the next line of code causes the run-time error 1004 if shL is not the last-active worksheet
    mtc = Application.Match(shE.Range("J3"), shL.Range(Cells(4, cc), Cells(y, cc)), 0)
        shE.Range("Q3").Value = WorksheetFunction.Index(shL.Range(Cells(4, c), Cells(y, c)), mtc) 'this is actually part of a loop so the shE.Range("Q3") is more like ("Q" & i)

For clarity, shE.Range("J3") stores the Attribute4 (selectable by the user) (found in shL 4th column of each Team) and shE.Range("D10") is the Team Name.

The issue I'm having is that the code works perfectly if shL is the last-active worksheet on Excel. However, if the last-active worksheet is shE (or any other worksheet), I will get the Run-time error 1004: Method 'Range' of object '_Worksheet' failed.

I don't understand why this code only works if shL is last-active. This will be used by several different people in my organization and they will not have shL visible to them.

What can I do to fix the run-time error, or is there a way to lookup/find/pull the information better?

Try fully qualifying all the ranges. For example Cells(... should be SheetCodeName.Cells(... — Ricardo Diaz, Jul 20 '20 at 11:34
In `VBA` you have to declare all your variables individually. so your `Dim c, cc, y, mtc as long` translates to: `mtc = Long` and all other variables in this line are of type `Variant`. Change this to: `Dim c As Long, cc As Long, y As Long, mtc as long` — Zac, Jul 20 '20 at 11:42
@RicardoDiaz - I think my cells are already fully qualified. The sheet is identified by the shL which has been specified in the Dim. But thanks anyway. — Marc, Jul 23 '20 at 14:48
@Zac - thanks for the feedback. I had a nagging suspicion about that, but glad of the confirmation. I have now amended all of my variables. It hasn't made a blind bit of difference to the functionality of this code, but at least my variables are properly referenced. Thanks — Marc, Jul 23 '20 at 14:50
@Marc just to clarify, this is not fully qualified: `shL.Range(Cells(4, cc), Cells(y, cc))` see this [answer](https://stackoverflow.com/a/18962945/1521579) — Ricardo Diaz, Jul 23 '20 at 15:29

score 0 · Accepted Answer · answered Jul 23 '20 at 14:59

This is the workaround I found. Not ideal, but it seems to work. In every attempt I made (Match and Find were both exhausted), the code worked great if I was on shL but not if I was elsewhere; the Match just wouldn't work consistently. So I decided against doing it in VBA!

'Variables
Dim shE As Worksheet, shL As Worksheet
Dim ClmnLtrc As String, ClmnLtrcc As String
Dim c As Long, cc As Long, y As Long, Q As Long
Dim FoundTeam As Range, FoundRange As Range

'Define variables
Set shE = Sheets("EnteredData")
Set shL = Sheets("Lookups")
Set FoundTeam = shL.Range("AB2:BX2").Find(what:=shE.Range("D10"))
    c = FoundTeam.Column
    cc = c + 3
    y = shL.Cells(shL.Rows.Count, c).End(xlUp).Row
    Q = shE.Range("Q" & Rows.Count).End(xlUp).Row
    ClmnLtrc = Split(Cells(1, c).Address, "$")(1)
    ClmnLtrcc = Split(Cells(1, cc).Address, "$")(1)

'Write the Index Match into the cell
shE.Range("Q3").Value = "=INDEX(Lookups!$" & ClmnLtrc & "4:$" & ClmnLtrc & y & ",MATCH(EnteredData!$P$" & i & ",Lookups!$" & ClmnLtrcc & "$4:$" & ClmnLtrcc & y & ",0))"

'Convert the result to text
    shE.Range("Q3:Q" & Q).Copy
    shE.Range("Q3").PasteSpecial Paste:=xlPasteValues

I hope this can help someone else one day. Just because we write in VBA, doesn't mean everything has to be in VBA.

Application.Match only working on Lookup tab active

1 Answers1