Attempting to use dynamic variables within XLOOKUP VBA

sax2play

Board Regular
Joined
Mar 13, 2021
Messages
52
Office Version
  1. 2016
Platform
  1. Windows
Hello All,

I am trying to use XLOOKUP, using all dynamic variables. The columns on the sheet may change position but the header is always the same. When I define all of the ranges and then attempt add the XLOOKUP function, I get a #NAME? error. Any help or suggestions would be greatly appreciated.

VBA Code:
Dim lastrow, lastcol, lastrow2, lastcol2 As Long
    Dim myRng2 As Range
    Dim mycell2 As Range
    Dim MyColl2 As Collection
    Dim myIterator2 As Variant
    Dim MyColl3, MyColl4, MyColl5 As Collection
    Dim myIterator3, myIterator4, myIterator5 As Variant
    Dim myRng3, myRng4, myRng5 As Range
    Dim mycell3 As Integer
    Dim wsReview As Worksheet
    Dim wsReview2 As Worksheet
    Dim colFound As Integer
    
    lastrow = WB.Sheets("Review").Cells(Rows.Count, "A").End(xlUp).Row
    lastcol = WB.Sheets("Review").Cells(1, Columns.Count).End(xlToLeft).Column
    lastrow2 = WB.Sheets("Review (2)").Cells(Rows.Count, "A").End(xlUp).Row
    lastcol2 = WB.Sheets("Review (2)").Cells(1, Columns.Count).End(xlToLeft).Column
    
    Set wsReview = WB.Worksheets("Review")
    Set wsReview2 = WB.Worksheets("Review (2)")
    Set MyColl2 = New Collection
    'Set MyColl3 = New Collection
    Set MyColl4 = New Collection
    Set MyColl5 = New Collection
    
    mycell3 = 2
    
    MyColl2.Add "Review Notes"
    'MyColl2.Add "Comments"
    'MyColl3.Add "NUMBER"
    MyColl4.Add "NUMBER"          'Column on different sheet
    MyColl5.Add "Review Notes"          'Column on different sheet
    
    colFound = WorksheetFunction.Match("NUMBER", Range(wsReview.Cells(1, 1), wsReview.Cells(1, lastcol)), 0)
    
    With wsReview2
        For k = 1 To lastcol2
            For Each myIterator4 In MyColl4
                If Cells(1, k) = myIterator4 Then
                    Set myRng4 = WB.Worksheets("Review (2)").Range(Cells(2, k), Cells(lastrow2, k))
                End If
            Next
        Next
    End With
    With wsReview2
        For l = 1 To lastcol2
            For Each myIterator5 In MyColl5
                If Cells(1, l) = myIterator5 Then
                    Set myRng5 = WB.Worksheets("Review (2)").Range(Cells(2, l), Cells(lastrow2, l))
                End If
            Next
        Next
    End With
    'With wsReview
        'For j = 1 To lastcol
            'For Each myIterator3 In MyColl3
                'If Cells(1, j) = myIterator3 Then
                    'Set myRng3 = Range(Cells(2, j), Cells(lastrow, j))
                    'For Each mycell3 In myRng3
                        'mycell3 = mycell3.Address
                    'Next
                'End If
            'Next
        'Next
    'End With
    wsReview.Select
    With wsReview
        For i = 1 To lastcol
            For Each myIterator2 In MyColl2
                If Cells(1, i) = myIterator2 Then
                    Set myRng2 = Range(Cells(2, i), Cells(lastrow, i))
                    For Each mycell2 In myRng2
                        mycell2.Value = "=XLOOKUP(Cells(mycell3, colFound),myRng4,myRng5,""Previous Notes Not Found"",0,1)"
                        mycell3 = mycell3 + 1
                    Next
                    
                End If
            Next
        Next
    End With

Thanks in advance for any help!!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,352
Office Version
  1. 365
Platform
  1. Windows
Your original code looks to have some other errors in it
I agree with that, but assuming they have been resolved, I wanted to comment on the following

The is assuming your sheet is still called "Review (2)" per your original code.
If it has been changed to Review2 then make the 2 changes in the below.

VBA Code:
mycell2.Formula = "=XLOOKUP(" & _
Cells(mycell3, colFound).Address & "," & _
"'Review (2)'!" & myRng4.Address & "," & _
"'Review (2)'!" & myRng5.Address & "," & _
"""Previous Notes Not Found""" & ")"

Assuming that myRng4 and myRng have eventually been correctly set to appropriate ranges in 'Review (2)' or 'Review2' or whatever it is now called earlier in the code, there is no need to include that sheet qualification in the vba formula code & therefore no need to worry about its name in this particular line of code. It can be handled as follows

Rich (BB code):
   myCell2.Formula = "=XLOOKUP(" & _
                        Cells(myCell3, colFound).Address & "," & _
                        myRng4.Address(External:=True) & "," & _
                        myRng5.Address(External:=True) & "," & _
                        """Previous Notes Not Found""" & ")"
 
Solution

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,623
Office Version
  1. 365
Platform
  1. Windows
myRng4.Address(External:=True)
I actually learnt that from Fluff not so long ago although Fluff normally uses Address(,,,1). The last time I tested in the immediate window it put the whole Workbook name in the output, which I was reluctant to do. Based on your suggestion I retried it and put it into a cell and it does seem to get rid of the redundant workbook reference in the cell.
So thank you, I will be more inclined to use it in the future.

vba converts all Integer values to Long even if they are declared as Integer, so you might as well declare them as Long to start with to save the bother of conversion. Besides, Long is shorter to type anyway
I initially got this from Paul Kelly from Excel Macro Mastery so I always use Long It's nice to hear that someone else subscribes to this approach.
 

sax2play

Board Regular
Joined
Mar 13, 2021
Messages
52
Office Version
  1. 2016
Platform
  1. Windows
Thank you both for these tips and corrections in the formula! It is working perfectly and I realized that it returns 0 because I have some lookup cells that are blank.

Thanks again for all the help!!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,352
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome. Glad we could help. Thanks for the follow-up. :)
 

sax2play

Board Regular
Joined
Mar 13, 2021
Messages
52
Office Version
  1. 2016
Platform
  1. Windows
So a couple of tips

1.
VBA Code:
Dim myRng3, myRng4, myRng5 As Range
This only declares myRng5 as Range. myRng3 and myRng4 will be allocated as Variant. If you want them all to be declared as Range, you need to list it out in full like this
VBA Code:
Dim myRng3 As Range, myRng4 As Range, myRng5 As Range

2.
VBA Code:
Dim colFound As Integer
Nothing at wrong with this technically. However, vba converts all Integer values to Long even if they are declared as Integer, so you might as well declare them as Long to start with to save the bother of conversion. Besides, Long is shorter to type anyway. ;)
VBA Code:
Dim colFound As Long
This is very helpful info - I have made the corrections throughout the rest of my code. Thanks!!
 

sax2play

Board Regular
Joined
Mar 13, 2021
Messages
52
Office Version
  1. 2016
Platform
  1. Windows
I resolved the issue with the function returning 0 by changing the lookup value column and adding additional functions for additional conditions:

VBA Code:
mycell2.Formula = "=IF(" & _
                                                Cells(mycell3, colFound).Address & "="""",""""," & _
                                                "IFNA(IF(LEN(XLOOKUP(" & _
                                                Cells(mycell3, colFound).Address & "," & _
                                                myRng4.Address(External:=True) & "," & _
                                                myRng5.Address(External:=True) & "))=0," & _
                                                """No Notes From Previous Meeting""" & "," & _
                                                "XLOOKUP(" & _
                                                Cells(mycell3, colFound).Address & "," & _
                                                myRng4.Address(External:=True) & "," & _
                                                myRng5.Address(External:=True) & "," & _
                                                """New Issue""" & "))" & "," & _
                                                """New Issue""" & "))"
mycell3 = mycell3 + 1

Thanks again for all the help!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,043
Messages
5,835,088
Members
430,340
Latest member
xoxe1337

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top