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

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

On Aug 15, 9:59*am, wrote:
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

OK theres really only one line - try using

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

instead of

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

Cliff Edwards

I have excel 2003 and you would need this change

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

" wrote:

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

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?

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

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?

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?

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)


wrote:

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

--

Dave Peterson

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