Nov 07 2020 08:44 PM
Nov 07 2020 08:44 PM
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?
Nov 08 2020 05:45 AMSolution
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)"
Nov 08 2020 07:10 AM
@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.
Nov 08 2020 08:44 AM
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")