type mismatch

[ExcelQuestion123]

I'm having an issues with this simple code and can't figure it out. I
always get the type mismatch error. I do have a range named
StatusTable, so that's not the problem. Please help.

Sub msg()
Dim i, j, k As Integer
i = 1
j = 3

If Application.VLookup(Worksheets(i).Range("A" & j) &
Worksheets(i).Range("D" & j), Range("StatusTable"), 2, False) = "Paid"
Then
MsgBox ("Correct")
Else: MsgBox ("Error")
End If

End Sub

 

[ward376]

One thing:

Dim i, j, k As Integer

You have to specify each dimensioned variable as a data type -

dim i as integer, j as long, k as integer

Which line generates the error?

Cliff Edwards

 

[ward376]

I'm having an issues with this simple code and can't figure it out.  I
always get the type mismatch error.  I do have a range named
StatusTable, so that's not the problem.  Please help.

Sub msg()
Dim i, j, k As Integer
i = 1
j = 3

If Application.VLookup(Worksheets(i).Range("A" & j) &
Worksheets(i).Range("D" & j), Range("StatusTable"), 2, False) = "Paid"
Then
    MsgBox ("Correct")
    Else: MsgBox ("Error")
End If

End Sub

 

[Joel]

I have excel 2003 and you would need this change

from
Application.VLookup(......
to
WorksheetFunction.VLookup(.....

 

[ward376]

OK theres really only one line - try using

(sheets(i).Range("A" & j)

instead of

(Worksheets(i).Range("A" & j)

Cliff Edwards

 

[ExcelQuestion123]

I have excel 2007 and the WorksheetFunction doesn't seem to work on
that.

The Worksheets --> sheets didn't change anything

I want both i and j to be integers and in excel 2007 I've always been
able to put them together like that.

Any other ideas?

 

[ward376]

You can put them together like that - but i and j are dimmed as
variants, not integers.

j should be dimmed long because you're using it for rows and row
numbers can exceed the parameters of the integer data type. (-32,768
to 32,767)

Try it and let us know what happens.

Cliff Edwards

 

[ExcelQuestion123]

I tried that; here is my code now:

Sub msg()
Dim i As Integer
Dim j As Long

i = 1
j = 3

If Application.VLookup(Worksheets(i).Range("A" & j) &
Worksheets(i).Range("D" & j), Range("StatusTable"), 2, False) = "Paid"
Then
MsgBox ("Correct")
Else: MsgBox ("Error")
End If

End Sub

and the error message i currently get is: "Method 'Range' of object
'_Global' failed."

Any other suggestions?

 

[ExcelQuestion123]

Nevermind on the last error message. I'm still getting the type
mismatch though. Could it have something to do with the data type/
format?

 

[Dave Peterson]

I'd use:

Option Explicit
Sub msg()
Dim i as long
dim j as long
dim k As long
dim res as variant
i = 1
j = 3

with worksheets(i)
res = Application.VLookup(Worksheets(i).Range("A" & j).value _
& Worksheets(i).Range("D" & j).value, _
.Range("StatusTable"), 2, False)

if iserror(res) then
msgbox "No match" 'or "Error" '????
elseif lcase(res) = lcase("Paid") then
msgbox "Correct"
else
msgbox "Error"
end if
End with

End Sub

(Untested, but it did compile.)

I put the statustable range on worksheets(i). If that's incorrect, then qualify
that range accordingly:

res = Application.VLookup(Worksheets(i).Range("A" & j).value _
& Worksheets(i).Range("D" & j).value, _
worksheets("somesheetnamehere").Range("StatusTable"), 2, False)

 

[ExcelQuestion123]

I was able to solve my problem with all the help from above. Thanks
so much!