Create VLOOKUP formula with variable worksheet

[GeorgeNFN]

Hello all,

I have come to a dead end in attempting to solve my issue, so I turn to you all for help.

I have created a sequence of macros to manage our golfing group’s bimonthly eclectic scores. Although it is amateurish and inefficient, it works. However, I am attempting to tidy it up and I want to create a formula, using vlookup, that will recover the final scores of the players, that are on the current round worksheet, back to the master data worksheet. The name of the current round worksheet is a variable and this is where the problem lies.

Sub test()
'
Dim NewRound As String, NewRoundLeaderboard As String

Sheets("MasterData").Select

'Following 4 lines for test only
NewRound = "r2019.6"
NewRoundLeaderboard = NewRound & "Leaderboard"
FirstRowMasterData = 9
LastRowMasterData = 24

For i = FirstRowMasterData To LastRowMasterData
    Cells(i, 4).FormulaR1C1 = "=if(isna(VLOOKUP(RC1,'NewRoundLeaderboard'!R11C1:R21C12,12,FALSE)),Template!r1c24,VLOOKUP(RC1,'NewRoundLeaderboard '!R11C1:R21C12,12,FALSE))"
Next
'
End Sub

Whilst I can see that the variable NewRoundLeaderboard is correctly constructed (by hovering the mouse over it), it does not show a value by hovering over it in the vlookup formula. When that line is executed, I get a Finder window with the message “Cannot find” NewRoundLeaderboard”. Copy from:”.

I have attempted different combinations of the ‘ (single apostrophe), “ (double apostrophe), & (ampersand) and spaces to get VBA to recognise that my variable NewRoundLeaderboard is the name of the worksheet but to no avail. Frustratingly, replacing the variable with the actual worksheet name (r2019.6Leaderboard) works OK.

I have also made other abortive attempts using Set (a range) and defining a Name. (range).

As a matter of interest, the value Template!r1c24 refers to a weeping emoji as I cannot find a way to create it directly.

I am using Mac OS 10.14.6 and Excel v16.32.

Any solutions would be gratefully appreciated.

 

[StephenCrump]

Welcome to the Forum

NewRoundLeaderboard is a valid string in VBA, but it means nothing to Excel. So you need to pass the value of NewRoundLeaderboard from VBA to Excel, like this:

Cells(i, 4).FormulaR1C1 = "=if(isna(VLOOKUP(RC1,'" & NewRoundLeaderboard & "'!R11C1:R21C12,12,)),Template!r1c24,VLOOKUP(RC1,'" & NewRoundLeaderboard & "'!R11C1:R21C12,12,))"

Or you could do it in one line, i.e. without selecting the worksheet or looping:

Sheets("MasterData").Range("D" & FirstRowMasterData & ":D" & LastRowMasterData).Formula = _
    "=if(isna(VLOOKUP(A" & FirstRowMasterData & ",'" & NewRoundLeaderboard & "'!$A$11:$L$21,12,)),Template!$X$1,VLOOKUP(A" & FirstRowMasterData & ",'" & NewRoundLeaderboard & "'!$A$11:$L$21,12,))"

I also believe you have access to the IFERROR function in Excel 16 for Mac?
This will allow you to simplify the IF(ISNA(YourFormula),ErrorValue,YourFormula) construction to IFERROR(YourFormula),ErrorValue)

 

[GeorgeNFN]

Hi Stephen,

Thanks for the reply. Unfortunately, even with your suggested enclosing of my variable NewRoundLeaderbaord with the single and double apostrophes, I still get the Finder window with the same message “Cannot find” NewRoundLeaderboard”. Copy from:”. I am, however, able to see that the value of the variable has been created inside the VLOOKUP formula by hovering the mouse over it. Because of this recurring problem, I have nor attempted your alternative solutions to simplify my code but I will do so when I get it fixed.

If you have any further suggestions, they would be most welcome.

George

 

[GeorgeNFN]

Hi Stephen,

I have a sincere apology to make. Your solution works. The repeated error that I got came from my failure to change the second VLOOKUP on that line of code. I could not see that part on my screen as I cannot get the normal line break in the code by using space and underscore on the Mac version of Excel VBA.

I will now progress to your other suggestions to the code. Thanks again.

George

 

[StephenCrump]

No problem. I'm glad you got it working.