VLOOKUP in VB

B

brett.kaplan

Hi,

I have a vlookup in VB and I'm pretty sure it's working, except it
returns a #N/A as the result. I'm pretty sure this is because of the
"format" I am using, but I'm not sure how to change it. I'm looking up
a date from one column, finding it in another and returning the second
column (a percent with up to 5 decimals). What should I be using to
get this to work:

Sub GetRates()

Dim Row As Integer
Dim Dates As Date
Dim Libor As Date

Row = 3

Dates = Sheets("Libor").Cells(Row, 2).Value
Libor = Application.VLookup(Worksheets("Libor").Cells(Row, 2).Value,
Worksheets("BB").Range("B:C"), 2, False)

Cells(Row, 3).Value = Libor


End Sub

I have Row as a variable because I will be adding to it.

Thanks,
Brett
 
D

Dave Peterson

I wouldn't use variables that look like VBA properties (or any reserved word).

Option Explicit
Sub GetRates()

Dim myRow As Long
Dim Dates As Date
Dim Libor As Variant 'could return an error

myRow = 3

Dates = Sheets("Libor").Cells(Row, 2).Value
Libor = Application.VLookup(Dates, Worksheets("BB").Range("B:C"), 2, False)
'if that doesn't help, try:
'Libor = Application.VLookup(clng(Dates), _
' Worksheets("BB").Range("B:C"), 2, False)

if iserror(libor) then
libor = "missing"
end if

Cells(Row, 3).Value = Libor

End Sub

====
Sometimes, VBA can have trouble with dates. If you use clng(dates), it might
work.
 
D

Dave Peterson

Ps. I didn't compile that or test it.

Hi,

I have a vlookup in VB and I'm pretty sure it's working, except it
returns a #N/A as the result. I'm pretty sure this is because of the
"format" I am using, but I'm not sure how to change it. I'm looking up
a date from one column, finding it in another and returning the second
column (a percent with up to 5 decimals). What should I be using to
get this to work:

Sub GetRates()

Dim Row As Integer
Dim Dates As Date
Dim Libor As Date

Row = 3

Dates = Sheets("Libor").Cells(Row, 2).Value
Libor = Application.VLookup(Worksheets("Libor").Cells(Row, 2).Value,
Worksheets("BB").Range("B:C"), 2, False)

Cells(Row, 3).Value = Libor

End Sub

I have Row as a variable because I will be adding to it.

Thanks,
Brett
Click to expand...
 
Top