Vlookup, excel, error 438 "object does not support this property or method"

Question

I have the following code:

• It is assumed that he enters the sheet with the name "Flash" and receives the i-th two-digit numerical value and extracts the value in the offset of column 4 to the right of Activecell.
• Then go to the sheet named "Sheet1" in the same book and use the "Vertical Search" function to find the value obtained and return the value of 4 columns to the right of this cell.

However, when I run the script below, it stops working with:

MsgBox (ActiveSheet.VLookup(LookFor, "A:A", 4, True))

and VBA throws error 438 object doesn't support this property or method

Does anyone know why an exception exists?

 ' Begin lookup : Dim i As Integer, designator As String, LookFor As String Flash.Activate ActiveSheet.Range("C3").Select For i = 3 To lastUsedCellInRow("C") designator = "C" + CStr(i) Dim cellVal As String cellVal = ActiveSheet.Range(designator).Value() If (Len(cellVal) <= 2 And IsNumeric(cellVal)) Then LookFor = ActiveSheet.Range(designator).Offset(0, 4).Value() RawData.Activate MsgBox (ActiveSheet.VLookup(LookFor, "A:A", 4, True)) End If Next i 

Answer 1

You have some problems

• You need to refer to the range as Range("A:A") not "A:A"
• If you use VLOOKUP , not LOOKUP , then, as indicated, you need to refer to the range of four columns, Range("A:D")
• You need to process your test value not found in A

Sample code below for adaptation

 Dim strLookfor as String Dim strOut strLookfor = "test" strOut = Application.VLookup(strLookfor, Range("A:D"), 4, True) If IsError(strOut) Then MsgBox "value not found" Else 'return column D value as string MsgBox CStr(strOut) End If 

Subsequent

Yes you could use

 `strOut = Application.VLookup(strLookfor & "*", Range("A:D"), 4, True)` 

for this match

bu I think Find cleaner, i.e.

 Dim strLookfor As String strLookfor = "F71" Dim rng1 As Range Set rng1 = Sheets("Sheet1").Columns("A").Find(strfolder & "*", , xlValues, xlPart) If Not rng1 Is Nothing Then MsgBox "Match in " & rng1.Offset(0, 3) Else MsgBox strfolder & "*" & vbNewLine & "not found" End If