VBA Lookup with Dynamic Range

Question

I'm looking for some help with running a VBA lookup Function. I have setup two search functions designed to find the start and end of the data and set that as the range of the vlookup. The part I'm having trouble with appears to be setting the range correctly. I have the integer values of the rows and the columns should remain standard. The data will be between Columns B and I.

The currently shown code appears to rather than set the boundries of the code in the range area, return the value of those cells which of courser results in an error. Thanks In advance :)

Does anybody know I would go about setting the range/fixing the vlookup ? the current error: unable to get the vlookup property of the worksheet function class

PotatoePriceEuro.value and lengthinputtext.value are textbox inputs on a userform. Truecheck is a global variable from earlier on inside the module, it contains the keyword searched for in the first two search functions.

The goal of the program is to search a sheet and find the first and last occurance of a particular string as given by a textbox in the userform (the string in truecheck) and then set that as the range for the vlookup. the vlookup is then passed a numeric term from another textbox on the userform (lengthinputtext.value) which then searches column C for that number and returns the value of the cell to its left. Note that the keyword for setting the range is in Column B and the length to be search within the range will be in column C

Private Sub optionselect()
    Dim LastLocation As Range
    Dim FirstLocation As Range
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim SearchVal As String
    Dim returnval As Integer

    Set FirstLocation = Range("B:B").Find(truecheck, LookIn:=xlValues, _ 
    LookAt:=xlWhole, SearchOrder:=xlByRows)

    Set LastLocation = Range("B:B").Find(truecheck, LookIn:=xlValues, _ 
    LookAt:=xlWhole, SearchOrder:=xlByRows, searchdirection:=xlPrevious)

    FirstRow = FirstLocation.Row
    LastRow = LastLocation.Row

    PotatoPriceEuro.Value = Application.WorksheetFunction.VLookup(LengthInputText.Value, _ 
    Range(Cells(FirstRow, 3), Cells(LastRow, 9)), 2, False)

End Sub

Truecheck is a global variable that holds the term to be searched in the first two find functions (one searching from the bottom one from the top) I'll try and edit in a better explanation — Morlo4, Jul 19 '16 at 04:18
Is [this question (http://stackoverflow.com/questions/19280477/unable-to-get-the-vlookup-property-of-the-worksheetfunction-class-error)](http://stackoverflow.com/questions/19280477/unable-to-get-the-vlookup-property-of-the-worksheetfunction-class-error) relevant? — YowE3K, Jul 19 '16 at 04:23
@dirk Riechel It is as you say a string identifier that runs down column A — Morlo4, Jul 19 '16 at 04:27
woops, my bad. The search term is actually in Column B as you rightly suggested it should be — Morlo4, Jul 19 '16 at 04:30
Are you sure that the value of `truecheck` and the value of `LengthInputText.Value` both exist in column B? And if LengthInputText.Value<>truecheck, does the value of truecheck exist **twice** in column B? — YowE3K, Jul 19 '16 at 04:36
they don't, I'll change that 2, as lengthinputtext.value exists in column 3. That was an error that has since been fixed and the same problem persists /: . the value of truecheck will exist a few times in column b, but the search function is supposed to only return the first and last (which if I check the values in the debugger it they do) — Morlo4, Jul 19 '16 at 04:38
If LengthInputText.Value (your "lookup" value) exists in column 3, then your range will need to be `Range(Cells(FirstRow, 3), Cells(LastRow, 9))` — YowE3K, Jul 19 '16 at 04:40
It was changed to that just after posting the code, but the problem persists — Morlo4, Jul 19 '16 at 04:43
please run `If IsError(Application.Match(LengthInputText.Value, Range("C" & FirstRow & ":C" & LastRow), 0)) Then MsgBox "there is no " & LengthInputText.Value & " somewhere at " & Range("C" & FirstRow & ":C" & LastRow).Address` in front of your lookup and tell if the msgbox is shown. — Dirk Reichel, Jul 19 '16 at 04:45
woohoo, you where right. "there is no somewhere at $C$11:$C$26". does that mean that it is just looking at those two points and not the stuff inbetween ? — Morlo4, Jul 19 '16 at 04:51
LOL - I guess the question I linked to earlier **was** relevant! :-) — YowE3K, Jul 19 '16 at 04:55
A VLOOKUP looks for your search term in the first column of the range (so all the cells between C11 and C26 in your example), then returns the value in that row in the xth column of the range (6th column in your code) — YowE3K, Jul 19 '16 at 04:56
weird thing is that there totally is something at C11:C26, maybe its looking at the wrong sheet. I'll post a screenshot (edited of course). also the above code is looking at the wrong index column but i'll fix that now (my actual code is already updated) — Morlo4, Jul 19 '16 at 04:57
There might be something there, but is there a "somewhere" there? — YowE3K, Jul 19 '16 at 04:58
Some things in this appear weird - earlier you said that LengthInputText.Value was `Numeric Decimal value !` but now the MsgBox has shown that it is a string value of `somewhere`. I think you need to check what values you are using. — YowE3K, Jul 19 '16 at 05:03
If you look at the code above, the "somewhere" is just a string he put in there to suggest its empty — Morlo4, Jul 19 '16 at 05:04
If I hover over the value of "Cells(firstrow, 3)" in the debugger it shows me that that cell contains 5.2. I would have thought that it would have showed be the range — Morlo4, Jul 19 '16 at 05:08
@morlo4 re "somewhere" - my apologies, I misread the debug code that Dirk gave you and thought that the "somewhere" was the value being returned from LengthInputText.Value — YowE3K, Jul 19 '16 at 06:40

score 0 · Answer 1 · answered Jul 19 '16 at 05:58

The final solution ended up with me giving up on using a vlookup and instead resorting to using a Find function and then offsetting it to retrieve the information, looking something like this (still not all cleaned up properly). I'm not sure why it took me so long to give up on the vlookup and why it wasn't working.

Private Sub optionselect()
    Dim LastLocation As Range
    Dim FirstLocation As Range
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim SearchVal As String
    Dim returnval As Integer
    Dim lastlocationoff As Variant
    Dim rng As Range
    Dim resultfind As Range
    Dim Columoff As Integer

    lengthvlook = LengthInputText.Value

    Set FirstLocation = Worksheets("Bucket  Elevator"_ 
    ).Range("B:B").Find(truecheck, LookIn:=xlValues, lookat:=xlWhole _ 
    , searchorder:=xlByRows)
    Set LastLocation = Worksheets("Bucket Elevator" _ 
    ).Range("B:B").Find(truecheck, LookIn:=xlValues, lookat:=xlWhole _ , 
    searchorder:=xlByRows, searchdirection:=xlPrevious)

    FirstRow = FirstLocation.Row
    LastRow = LastLocation.Row

    Set resultfind = Worksheets("Bucket Elevator").Range("C" & FirstRow & _ 
    ":C" & LastRow).Find(LengthInputText.Value, LookIn:=xlValues, _ 
    lookat:=xlWhole, searchorder:=xlByRows)

    PektusPriceEuro = Worksheets("bucket elevator").Cells(resultfind.Row, 4)

End Sub

for your case `PektusPriceEuro = Worksheets("Bucket Elevator").Range(FirstLocation, LastRow).Offset(0, 1).Find(...).Offset(0, 1).Value` also will do... saves up some lines ;) — Dirk Reichel, Jul 19 '16 at 06:21

VBA Lookup with Dynamic Range

1 Answers1