VBA - Need help adding results of two Vlookup statements...

[Lidsavr]

I am using Excel 2007 (code must be able to run from Excel 2003 as well).

I have two Vlookup statements. I would like to add the results of both, but what I have is not working. The first example below returns a #NAME! error. The second returns 0 when it should be returning a value of 243 (there is data in the table for both statements).

Here are two examples of the code I have tried so far:

    Dim mnth1 As String
    Dim mnth2 As String
    Dim mnth3 As String

[COLOR="Green"]  ' More code follows the variables above until it gets here:[/COLOR]

    Sheets("1145_1145B Data").Select
    Range("S2").Select
    On Error Resume Next
    x = Application.WorksheetFunction.VLookup(mnth1 & "-ARCT01145-F1", _
                Worksheets("<").Range("A1:H65536"), 7, False)
    If Err.Number <> 0 Then
        x = 0
    End If
    y = Application.WorksheetFunction.VLookup(mnth1 & "-ARCT01145B-F1", _
                Worksheets("<").Range("A1:H65536"), 7, False)
    If Err.Number <> 0 Then
        y = 0
    End If
    On Error GoTo 0
    Worksheets("1145_1145B Data").Range("S2").Value = x + y

[COLOR="Green"]  ' More code follows the code above...[/COLOR]

This returns a #NAME! error


I modified the code and tried the following:

    Dim mnth1 As String
    Dim mnth2 As String
    Dim mnth3 As String

[COLOR="Green"]  ' More code follows the variables above until it gets here:[/COLOR]

    Sheets("1145_1145B Data").Select
    Range("S2").Select
    On Error Resume Next
    x = Application.WorksheetFunction.VLookup(mnth1 & "-ARCT01145-F1", _
                Worksheets("<").Range("A1:H65536"), 7, False) + _
                Application.WorksheetFunction.VLookup(mnth1 & "-ARCT01145B-F1", _
                Worksheets("<").Range("A1:H65536"), 7, False)
    If Err.Number <> 0 Then
        x = 0
    End If
    On Error GoTo 0
    Worksheets("1145_1145B Data").Range("S2").Value = x

[COLOR="Green"]  ' More code follows the code above...[/COLOR]

This returns a value of zero.

TWO NOTES:

1. Each Vlookup statement works great alone with the code. In the two codes, I have an error statement to return the value of zero if the data is not in the table. This must be in the statement.
2. If statement 1 returns 177 and Statement 2 returns 66 then the sum should be 243. If statement 1 returns 0 and statement 2 returns 66 then the sum of the two should be 66..."

Thank you in advance for your help.

Charles

 

[JoeMo]

Try this:

Sheets("1145_1145B Data").Select
    Range("S2").Select
    If IsError(Application.WorksheetFunction.VLookup(mnth1 & "-ARCT01145-F1", _
                Worksheets("<").Range("A1:H65536"), 7, False)) Then
        x = 0
    Else
        x = Application.WorksheetFunction.VLookup(mnth1 & "-ARCT01145-F1", _
                Worksheets("<").Range("A1:H65536"), 7, False)
    End If
    'Repeat for y
    Worksheets("1145_1145B Data").Range("S2").Value = x + y

 

[Lidsavr]

JoeMo,
The code works great now. Thank you for replying. You have saved me a lot of time!

Charles