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.
Thanks in advance for any help!!
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 WithThanks in advance for any help!!
