VBA code - XLookup

[Danak995]

Hello, TY in advance for helping me.  I am learning to code VBA and looking for a VBA solution...I understand Xlookup in regular Excel.

I've included a data sample and the code.  The first half of the code works fine and retrieves the min or max value as asked for column H.  But my Xlookup isn't working for the three highlighted cells in column G.  I've googled my little heart out but can't figure out how to make it find the values in column H to retrieve the item name associated with it.  I've tried Cells(2,8) and I've tried MaxValue2.  

I've added the file and a screenshot of it.  Any thoughts?

[#2 · Accepted solution]

@Danak995 

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)"

[#3]

@Peter Bartholomew  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.

[#4]

@Peter Bartholomew  I'm so happy!  This finally worked:

Range("G2") = WorksheetFunction.xlookup(MaxValue, [C:C], [A:A], "Not found")

Thank you for your guidance!!!

[#5]

@Danak995 

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")