CODEWITHSUNDEEP
vbaexcel
James
James

Reputation: 509

Vlookup Dates in Excel VBA

I am working with 3 excel sheets. In sheet Start Page i have Dates starting from column A4 going down. The macro Vlooks up in sheet Fund Trend for the same Dates which are locate in column A11 to lastrow and offsets 3 columns , and copies the Value into sheet "Accrued Expenses" starting from Range("C7"). Macro loops until the lastrow in sheets("Start page") Range("A4") .

The Problem is that the macro is not populating the values into sheet Accrued expenses, on some occasions. OR its not finding the Date. My code is below:

Sub NetAsset_Value()
Dim result As Double
Dim Nav_Date As Worksheet
Dim fund_Trend As Worksheet
Dim lRow As Long
Dim i As Long

Set Nav_Date = Sheets("Start page")
Set fund_Trend = Sheets("Fund Trend")
lRow = Sheets("Start page").Cells(Rows.Count, 1).End(xlUp).row

 For i = 4 To lRow



result = Application.WorksheetFunction.VLookup(Nav_Date.Range("A" & i), fund_Trend.Range("A11:C1544"), 3, False)

            Sheets("Accrued Expenses").Range("C" & i + 3).Value = result
             Sheets("Accrued Expenses").Range("C" & i + 3).NumberFormat = "0.00"
         Sheets("Accrued Expenses").Range("C" & i + 3).Style = "Comma"




    Next i

End Sub

Error Trap:

On Error Resume Next

result = Application.WorksheetFunction.VLookup(Nav_Date.Range("A" & i), fund_Trend.Range("A11:C1544"), 3, False)

        If Err.Number = 0 Then

            Sheets("Accrued Expenses").Range("C" & i + 3).Value = result
             Sheets("Accrued Expenses").Range("C" & i + 3).NumberFormat = "0.00"
         Sheets("Accrued Expenses").Range("C" & i + 3).Style = "Comma"
        End If


        On Error GoTo 0

To over come the Date issue i have this sub dont know if this is efficient? 

Sub dates()
Sheets("Start page").Range("A4", "A50000").NumberFormat = "dd-mm-yyyy"
Sheets("Fund Trend").Range("A11", "A50000").NumberFormat = "dd-mm-yyyy"
End Sub

The issue that i am now having is that when enter a date like 11/02/2015 it switches to 02/11/2015. But its not happening to all Dates

Overcoming the Problem. I am placed a worksheet function to force the date columns to text. Which is currently working. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Start page").Range("A4", "A50000").NumberFormat = "@"
Sheets("Fund Trend").Range("A11", "A50000").NumberFormat = "@"
End Sub

Upvotes: 2

Views: 1841

Answers (1)

David Zemens
David Zemens

Reputation: 53663

To avoid the 1004 error you can use the Application.VLookup function, which allows an error type as a return value. Use this method to test for an error, and if no error, return the result.

To do this, you'll have to Dim result as Variant since (in this example) I put a text/string value in the result to help identify the error occurrences.

If IsError(Application.Vlookup(Nav_Date.Range("A" & i), fund_Trend.Range("A11:C1544"), 3, False)) Then
    result = "date not found!"
Else
    result = Application.WorksheetFunction.VLookup(Nav_Date.Range("A" & i), fund_Trend.Range("A11:C1544"), 3, False)
End If

The "no result printed in the worksheet" needs further debugging on your end. Have you stepped through the code to ensure that the result is what you expect it to be, for any given lookup value? If there is no error, then what is almost certainly happening is that the formula you have entered is returning a null string and that value is being put in the cell.

Upvotes: 1

Related Questions