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)"
PeterBartholomew1 Thank you for taking pity on a learner. I don't really understand what you mean by Option Explicit to force the declaration of variables. It sounds like an important concept though as I move forward. If you don't mind I'd like to understand a bit more.
I tried putting brackets around [H8],[C:C], etc. I don't want to use [C2:C8] because the worksheets have differing number of rows and I'd like it to just search the entire column. My result is that the cells G2 and G3 return nothing. Not even "not found". I'm perplexed.
Option Explicit
Under Tools/Options/Editor there is an option button "Require Variable Declaration". Once you set it, whenever you load a code module, it should start with Option Explicit. The code will then only run if all variables are declared. In this case
Dim MaxValue As Double, MaxValue2 As Double
Dim MinValue As Double
Certainly references such as A:A have a place in traditional Excel. I think some care is needed because some array formulas will actually perform the 1048575 calculations, just as requested. The Table has the advantage of adjusting dynamically as you add data.
Dim Rng As Range, Change As Range, Volume As Range, Item As Range
Set Rng = Worksheets(1).ListObjects(1).DataBodyRange
With Rng
Set Change = .Columns(3)
Set Volume = .Columns(4)
Set Item = .Columns(1)
End With
...
[G2] = WorksheetFunction.XLookup(Cells(2, 8), Change, Item, "Not found")
[G3] = WorksheetFunction.XLookup(Cells(3, 8), Change, Item, "Not found")
[G4] = WorksheetFunction.XLookup(MaxValue2, Volume, Item, "Not found")