Forum Discussion
VBA code - XLookup
The line
Range("G2") = WorksheetFunction.XLookup(Cells(2, 8), "C2:C8", "A2:A8", "Not found")has strings as parameters rather than range references
Range("G2") = WorksheetFunction.XLookup([H8], [C2:C8], [A2:A8], "Not found")where [A2:A8] is an alternative notation for Range("A2:A8").
Other comments are that it is good practice to use Option Explicit to force the declaration of variables; I prefer the use of Defined Names or Tables (List Objects) to the direct referencing to avoid problems when rows are added or deleted to the sheet.
Given the choice, I would use worksheet functions directly within the grid rather than from VBA, but I appreciate this is a learning exercise. Some thing that may be of interest is that it is possible to write the worksheet formulas from VBA.
Range("G2").Formula = "= XLookup(H2, C2:C8, A2:A8)"
The line
Range("G2") = WorksheetFunction.XLookup(Cells(2, 8), "C2:C8", "A2:A8", "Not found")has strings as parameters rather than range references
Range("G2") = WorksheetFunction.XLookup([H8], [C2:C8], [A2:A8], "Not found")where [A2:A8] is an alternative notation for Range("A2:A8").
Other comments are that it is good practice to use Option Explicit to force the declaration of variables; I prefer the use of Defined Names or Tables (List Objects) to the direct referencing to avoid problems when rows are added or deleted to the sheet.
Given the choice, I would use worksheet functions directly within the grid rather than from VBA, but I appreciate this is a learning exercise. Some thing that may be of interest is that it is possible to write the worksheet formulas from VBA.
Range("G2").Formula = "= XLookup(H2, C2:C8, A2:A8)"
PeterBartholomew1 I'm so happy! This finally worked:
Range("G2") = WorksheetFunction.xlookup(MaxValue, [C:C], [A:A], "Not found")
Thank you for your guidance!!!