Stuck on VLOOKUP VBA using variables

[agon7171]

I've been struggling with this one for a few days now:

Trying to have vlookup in code search within a range, see below, I have first row cell and last row cell reference, however; those values are not working in vlookup for some reason.

'Obtain First cell
ActiveSheet.Cells(3, 3).Select
MyFirstRow = ActiveCell.Address


'Obtain Last Cell
ActiveSheet.Cells(3, 3).Select
Selection.End(xlDown).Select
MyLastRow = ActiveCell.Address
Selection.End(xlUp).Select


'Populate cell references
ActiveSheet.Cells(5, 8).Value = MyFirstRow
ActiveSheet.Cells(6, 8).Value = MyLastRow


ActiveSheet.Cells(3, 2).Select


ActiveCell.Formula = "=VLOOKUP($A1,MyFirstRow:MyLastRow,2,FALSE)"

My goal is the following:

LIST OF VALUES TO SEARCH:
ABC
DAA
FEE
FDC
EEE

RANGE:
ADK 1
DAL 2
ETT 5
ABC 3

Search all values within the range, I have a loop and works fine, just need to figure out how to reference vlookup using variables. Appreciate any help I can get, thanks.

[MINCUS1308]

I would use the .find() method.

[MINCUS1308]

Sub Test()
     With ActiveSheet.Columns(3).Cells
          Set c = .Find()           

          If Not c Is Nothing Then
               'code for if the value is found
          End If
     End With
End Sub

Something like the above is a good start.
you could build a 1D array of the values you wish to find
and loop the .find method as you step through the arrays values

[MINCUS1308]

slowly but surely ill figure this out. lol

Sub Test()
     Dim MyStrs As Variant
     MyStrs = Array("ABC", "DAA", "FEE", "FDC", "EEE")
     
     With ActiveSheet.Columns(3).Cells
          For I = LBound(MyStrs) To UBound(MyStrs)
               Set c = .Find(MyStrs(I))
               MsgBox I
               If Not c Is Nothing Then
                    'code for if the value is found
               End If
          Next I
     End With
End Sub

[MINCUS1308]

OK,
I need a little clarification on what you are trying to accomplish.
You have a list of values that you are looking for: "ABC", "DAA", "FEE", "FDC", "EEE"

And you have 4 different ranges that you would like to search: "ADK 1", "DAL 2", "ETT 5", "ABC 3"
??none of those are valid range names??

Could you share the workbook that you are working with?

[MINCUS1308]

Here is the file I've been playing around with: Help.xlsm
if the value is found in the specified range it will msgbox the value in the cell to the right
Hopefully this is what you were trying to accomplish.
Let us know if you need more help

sheet1 corresponds with sub Test
and will find the values in a column

sheet2 corresponds with sub TestTwo
and will find the values in the worksheet

Sub Test()
     Dim MyStrs As Variant
     MyStrs = Array("ABC", "DAA", "FEE", "FDC", "EEE")
     Sheet1.Select                                                        '<<<<<<<<<<< Look at sheet 1
     With Sheet1.Columns(3).Cells                                '<<<<<<<<<<<  Here is where I say look in column 3 on sheet 1
          For I = LBound(MyStrs) To UBound(MyStrs)
               Set C = .Find(MyStrs(I))
               If Not C Is Nothing Then
                    MsgBox C & " " & C.Offset(0, 1).Value
               End If
          Next I
     End With
End Sub

Sub TestTwo()
     Dim MyStrs As Variant
     MyStrs = Array("ABC", "DAA", "FEE", "FDC", "EEE")
     Sheet2.Select                                                        '<<<<<<<<<<< Look at sheet 2
     With Sheet2.Cells                                                  '<<<<<<<<<<<  Here is where I say look at all used cells in sheet 2
          For I = LBound(MyStrs) To UBound(MyStrs)
               Set C = .Find(MyStrs(I))
               If Not C Is Nothing Then
                    MsgBox C & " " & C.Offset(0, 1).Value
               End If
          Next I
     End With
End Sub

[agon7171]

Thanks so much for helping me. However, I don't know before hand what the values are and could be over 1500 rows in Excel. I have the first and last row cell reference, I need a vlookup to search based on first and last row. Reason for this is that each time program runs the number of rows change, thus I capture first and last and store in variables



'Obtain First cell
ActiveSheet.Cells(3, 3).Select
MyFirstRow = ActiveCell.Address


'Obtain Last Cell
ActiveSheet.Cells(3, 3).Select
Selection.End(xlDown).Select
MyLastRow = ActiveCell.Address
Selection.End(xlUp).Select


'Populate cell references
ActiveSheet.Cells(5, 8).Value = MyFirstRow
ActiveSheet.Cells(6, 8).Value = MyLastRow


ActiveSheet.Cells(3, 2).Select




ActiveCell.Formula = "=VLOOKUP($A$3,MyFirstRow:MyLastRow,2,FALSE)"

[MINCUS1308]

I'm sorry, I do not understand what it is that you are trying to accomplish.
Can you share your workbook?

[MINCUS1308]

based on the formula you have there -
it looks like you want to look up the value in A3 from the range "myfirstrow" to "mylastrow" and then return value from the second column and you don't need an exact match.

the reason your vlookup isn't work is because there is no column 2 in your range. (you've defined a range that is one column wide)

[MINCUS1308]

Run this:

Sub test()
     LookUpValue = Cells(1, 1).Value
     
      With ActiveSheet.Columns(3).Cells
          Set C = .Find(LookUpValue)
            If Not C Is Nothing Then
                Cells(3, 2).Value = C.Offset(0, 1).Value
                'MsgBox C & " " & C.Offset(0, 1).Value
            Else
                MsgBox "LookUpValue: '" & LookUpValue & "' not found."
            End If
      End With
     
End Sub

Here is a test file for you to play with: Book1.xlsm

[papermonkey]

using below am always getting value zero.
i even tried changing cell formats for datarange, when i do that i get 13 type mismatch error. please help.

If Trim(CStr(TextBox_itemcode.Text)) <> "" Then
myitval = mylookup(Trim(CStr(TextBox_itemcode.Text)))
Worksheets("SALES").Activate
Range("amt_tot").Rows(ZeileA).Value = myitval
Else
Range("amt_tot").Rows(ZeileA).Value = "9999.99"
End If




Function mylookup(itemcode As String) As Double
Dim myrange As Range
Dim myvalue As Double
Set myrange = Worksheets("Items sales").Range("G24:F1000")
Worksheets("Items sales").Activate
RCompVal = [Application.WorksheetFunction.SVERWEIS(itemcode, myrange, 5, False)]
If IsError(myvalue) = True Then
myvalue = 9999.99
End If
End Function

[MINCUS1308]

I think your problem is that your function is attempting to return a double but you're not returning anything.
You should really post this as a new thread. This thread was resolved - but ever marked as [SOLVED]

you

Dim myvalue As Double

and then check

If IsError(myvalue) = True

you don't ever actually do anything with

myvalue