Excel VBA vlookup using Dates

Question

I am working with three sheets. Worksheet Start Page has dates from A4 to lastrow. I have a Fund trend sheet with dates from A11 to last row. The vlookup is searching for Dates in the Fund trend sheet based on the list of Dates in the start page sheet. The search table range in the Fund trend sheet is Range(A11:C11) to lastrow. When the date is found it offsets (3,0), and that value is presented is sheet Acurred Expenses Range("C7"). This will loop till the lastrow in sheet start page A4.

=VLOOKUP('Start page'!A4,'Fund Trend'!A11:C21,3,0)

=VLOOKUP('Start page'!A5,'Fund Trend'!A12:C22,3,0)

as code i have not been successful:

Sub equity() 

Dim Nav_date As Date 

Dim equity As Integer 

Nav_date = Sheets("Start page").Range("A4") 

equity = Application.WorksheetFunction.VLookup(Nav_date,_
 Worksheets("Fund Trend").Range("A11:C12"), 3, False) 

Sheets("Acurred Expenses").Range("C7") = equity 

End Sub

What kind of error are you getting? You might need to set Nav_date = ... to ...("A4").value (add .value). — BruceWayne
@3578951 run time error 1004 - unable to get the Vlookup property of the worksheet Function class. — James

Soulfire Soulfire · Accepted Answer · 2015-04-24T17:33:52

I think this answer can be broken down into three parts: correctly referencing the properties of a Range object, retrieving the last row of data, and using a loop

Correctly referencing the range's value:

The first thing that I noticed is that you are attempting to assign a Date variable as a Range object.

This line:

Nav_date = Sheets("Start page").Range("A4")

Should be:

Nav_date = Sheets("Start page").Range("A4").Value

A Range is an object with has properties and methods. You must explicitly reference what it is about the range you want to get. It's value, it's cell address, etc.

Likewise this incorrect syntax is repeated below. The line:

Sheets("Acurred Expenses").Range("C7") = equity

Should be:

Sheets("Acurred Expenses").Range("C7").Value = equity

EDIT: Per the comments whytheq raises the point of default properties. Technically the code Sheets("Acurred Expenses").Range("C7") = equity is not incorrect, and will work, because the default property of the range is Value. I tend to prefer to be more explicit, but that is my personal preference so I always use Range.Value so there is not ambiguity. Either way should work though!

Retrieving the last row of the worksheet

To find the last used row of the data in the worksheet, we can start at the bottom of the workbook and "look up" until we find the first row (which will correspond to the last row of the data in the worksheet).

This code would be the same as activating the last cell in column A and them pressing CTRL+Shit+↑

Sub LastRow()

    Dim lRow As Long

    lRow = Cells(Rows.Count, 1).End(xlUp).Row

    Debug.Print lRow

End Sub

To reiterate, this starts at the very bottom row and goes all the way up, returning the row number of where it stops. This corresponds to the last value entered in column A. You might need to change A if your data is in a different column.

The loop

Finally, we can put everything we've learned together. After you have lRow which corresponds to your last row in your set of data we can perform a look for the VLOOKUP like so:

Sub equity()

    Dim Nav_date As Date
    Dim equity As Integer
    Dim lRow As Long
    Dim i As Long

    lRow = Sheets("Start page").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 4 To lRow 'Begin in Row 4 of the "Start page" sheet
        Nav_date = Sheets("Start page").Range("A" & i).Value

        'Tell code to continue even if error occurs
        On Error Resume Next

        equity = Application.WorksheetFunction.VLookup(Nav_date, _
         Worksheets("Fund Trend").Range("A11:C12"), 3, False)

        'Check the results of the VLOOKUP, an error number of 0 means no error
        If Err.Number = 0 Then
            'Here I use i+3 because the data started in row 7 and I assume
            'it will always be offset by 3 from the "Start Page"
            Sheets("Acurred Expenses").Range("C" & i + 3).Value = equity
        End If

        'Return to normal error handling
        On Error GoTo 0

    Next i

End Sub

Excel VBA vlookup using Dates

1 Answers