Vlookup with VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have to do a vlookup with VBA in one worksheet in which the Table_Array
will go from column A11 to L11 down to a row, containing a specific text,
"Paid." is that possible
 
Assuming that "paid" is in column L

R = Application.Match("paid", Range("L11").resize(1000,0),0) + 10
Set LookupTable = Range("All","L" & R)
X = Application.VLOOKUP("Jackson",LookupTable,11,0)
 
Myrna Larson said:
Assuming that "paid" is in column L

R = Application.Match("paid", Range("L11").resize(1000,0),0) + 10
Set LookupTable = Range("All","L" & R)
X = Application.VLOOKUP("Jackson",LookupTable,11,0)



Thank you your reply.
Click to expand...

I'm having the following error message "Run-time 1004"
Help me
Jeff
 
Hi
what is your complete code which you have tried and which line raises
this error?
 
The error is in this line:

R = Application.Match("paid", Range("L11").resize(1000,0),0) + 10

It should be
R = Application.Match("paid", Range("L11").resize(1000,1),0) + 10

Sorry for the typo.
 
I tested this one <g>:

Sub Test()
Dim R As Long
Dim LookupTable As Range
Dim X As Variant

R = Application.Match("paid", Range("L11").Resize(1000, 1), 0) + 10
Set LookupTable = Range("A11:L" & R)

X = Application.VLookup("Jackson", LookupTable, 11, 0)
Debug.Print R, LookupTable.Address, X
End Sub
 
Thank you very much. It worked
Jeff
Myrna Larson said:
I tested this one <g>:

Sub Test()
Dim R As Long
Dim LookupTable As Range
Dim X As Variant

R = Application.Match("paid", Range("L11").Resize(1000, 1), 0) + 10
Set LookupTable = Range("A11:L" & R)

X = Application.VLookup("Jackson", LookupTable, 11, 0)
Debug.Print R, LookupTable.Address, X
End Sub
Click to expand...
 
Hi,

Would it be possible to replace "jackson" by a row number, e629 for example
in this formula?
X = Application.VLookup("Jackson", LookupTable, 11, 0)
Thanks,
 
Thank you Franck,

Is it possible in the the formula below to start the range 1 row below a row
with text "paid" in column "E" all the way down to the next empty row in the
column "E" ?
 
Hi
you may use application.match to locate this row number and then use
this value as your row reference
 
Hi
try something like
y=application.match("paid",range("E:E"),0)
if no iserror(y) then
y = y +1
x = application.vlookup(range("E" & y),LookupTable, 11, 0)
if not iserror(x) then
msgbox x
end if
end if
 
Thank you it worked.

Is there VBA formula to automatically increment by 1 " y = y +1" ?
Thanks,
Jeff
 
Hi
no there's no such formula. You probably mean something like
y++
that is not available in VBA
 
Back
Top