VLookup in VBA

[Westbury]

I have what should be a simple lookup but get a "Unable to get the VLookup property of the Worksheet Function Class 1004 error message

I'm trying to look up the fiscal year in which a reading date occurs.

I've simplified my code to get this going and to test...

Dim readingdate As Date
Dim FiscYr As Long

readingdate = Worksheets("data input").Range("B400")

FiscYr = Application.WorksheetFunction.VLookup(readingdate, Worksheets("Lookups").Range("B7:D19"), 3, False)


Can someone give help me to correct this please?

Thanks
Geoff

 

[kweaver]

I think that even tho your B400 is a date, you need the DIM to make it LONG.

 

[Westbury]

thanks for the suggestion but it returns the same error.

 

[kweaver]

Is the worksheet "lookups" or "data input" ? I changed it to "data input" and it worked just fine.
There was data in B7:D19 and the year in B400 in my test. It also worked fine when I change the data range to another sheet.
Maybe it would help if you used XL2BB and showed some of your data.

 

[Westbury]

The lookup is a 3 column table on the ws "Lookups", the first 2 cols contain the start and end date for each financial year, the 3rd col is the fiscal year name e.g. 20/21. I'm asking Excel to find the row where the reading date is to find the respective fiscal year.
readingdate is on ws "data input"

 

[kweaver]

So, the VLOOKUP is looking at the FIRST column of the range for the date in B400. Is that where B400 will be found?

 

[Westbury]

No, the 1st column of the table is the financial year start date, the 2nd col is the end date and the 3rd col is the financial year name e.g.20/21 This works as an Excel function but isn't translating into vba for me. But I've now thought of a different solution avoiding a table that needs managing into the future.


Sub FiscYear()

Dim readingdate As Date
Dim FiscYr As String

readingdate = Worksheets("data input").Range("B400")

If Month(readingdate) >= 4 And Month(readingdate) <= 12 Then
FiscYr = Right(Year(readingdate), 2) & " " & Right(Year(readingdate), 2) + 1

Else
If Month(readingdate) >= 1 And Month(readingdate) <= 3 Then
FiscYr = Right(Year(readingdate), 2) - 1 & " " & Right(Year(readingdate), 2)

End If
End If

End Sub


This code will work for any year respective to the "reading date" to find the (British) financial year. Readers in other parts of the world will have to alter the month numbers in the code to suit their needs!

I'll incorporate this into other code which already reads the "reading date"

Thanks for your interest and comments
Geoff