Runtime error 13 with vlookup in vba

[bwar88]

Hi

Any chance you could help me with one more thing...my code below keeps returning a runtime error 13 'type mismatch'. varX has been declared as a variant. Basically the error occurs when I (knowingly) try to do a vlookup on an empty cell. What's the best way to get around this - I just want to set varX = 0 if an error occurs?

For j = 1 To UBound(strTheBoats)
            varX = Application.IfError(Application.VLookup(Sheet2.Cells(j + 1, k + strTheBoats(i).MaxWait), Sheet1.Range("A:D"), 4, 0), 0)
            dblHrsTotal = dblHrsTotal + varX
            varX = Application.IfError(((Application.VLookup(Sheet2.Cells(j + 1, k + strTheBoats(i).MaxWait), Sheet1.Range("A:D"), 5, 0)) ^ 2), 0)
            dblHrsVarTotal = dblHrsVarTotal + varX
        Next j

 

[Norie]

Does this work?

For j = 1 To UBound(strTheBoats)

            varX = Application.VLookup(Sheet2.Cells(j + 1, k + strTheBoats(i).MaxWait), Sheet1.Range("A:D"), 4, 0)
            If Not IsError(varX) Then
                dblHrsTotal = dblHrsTotal + varX
            End if

            varX = Application.VLookup(Sheet2.Cells(j + 1, k + strTheBoats(i).MaxWait), Sheet1.Range("A:D"), 5, 0)
            If Not IsError(varX) Then
                dblHrsVarTotal = dblHrsVarTotal + varX ^ 2
            End If
Next j

 

[Andrew Poulsom]

What's strTheBoats(i).MaxWait? On a quick test looking up a blank cell didn't create a type mismatch.

 

[bwar88]

Thanks Norie that fixed it!

 

[Andrew Poulsom]

Thanks Norie that fixed it!

Are you sure? If you get a type mismatch with your code I think you would also get one with Norie's code.

 

[Norie]

Andrew

Where would the type mismatch happen in the code I posted?

 

[Andrew Poulsom]

Well I think the type mismatch may be caused by this:

k + strTheBoats(i).MaxWait

As I said I don't get a type mismatch by trying to look up a blank cell with the original code. But, of course, I may be wrong.

 

[Norie]

Andrew

I thought the error might have been with the ^2.

Whatever it is/was I think it's a good idea to separate this type of code out a bit to either fix it or help discover what the actual problem is.