Nu 2 VB -- Type Mismatch Error

[Lukie]

I inherited an Excel spreadsheet with a macro that is giving me a type mismatch error on the line with 'If Cells(I, 1) <> "" Then'. I believe the programming is looking for a variable when the accompanying spreadsheet only contains text. If this is true, how do I resolve?

[CODE=vb]Dim Risks(20) As String
Dim LH(20), Impact(20), Response(20), Trend(20), Change(20), NR(20), Del(20), XY(3, 3), XAxis(3, 3) As Integer
Dim I, J, K, Flag, HI, MI, LI, RMax, Total, X, Y As Integer


I = 10
J = 1
Flag = 0
LI = 0
MI = 0
HI = 0

X = 1
Y = 1
While X <= 3
While Y <= 3
XY(X, Y) = 0
XAxis(X, Y) = 0
Y = Y + 1
Wend
Y = 1
X = X + 1
Wend


While Flag = 0

Sheets("Inputs" ).Select

If Cells(I, 1) <> "" Then

Risks(J) = Cells(I, 1)
LH(J) = Cells(I, 2)
Impact(J) = Cells(I, 3)

XY(LH(J), Impact(J)) = XY(LH(J), Impact(J)) + 1


Response(J) = Cells(I, 4)
Trend(J) = Cells(I, 5)
Change(J) = Cells(I, 6)
NR(J) = Cells(I, 7)
Del(J) = Cells(I, 8)

I = I + 1
J = J + 1
Else

Flag = 1
End If
Wend

Total = J - 1


RMax = 0

X = 1
Y = 1

While X <= 3
While Y <= 3
If XY(X, Y) > RMax Then
RMax = XY(X, Y)
End If[/CODE]

[Killer42]

My first thought would be, perhaps the cell in question contains a null, or an error, or even a number. Since you're comparing it to a string, the null or error would quite probably cause a problem. As for the number... maybe, maybe not. Anyway, I'd recommend you go into debug mode at the point where the error occurs, and examine the contents of Cells(I, 10).


An interesting side note is that unless this is the newer VB.Net syntax (which it certainly dodsn't look like), only XAxis() and Y are defined as Integer. All the rest (apart from Risks() which is String, of course) are defined as Variant, the default type. Were you aware of that?

I did wonder at first whether perhaps this meant that I was of the wrong type and thus producing the error. But the fact that you're placing a numeric value in it (@ line 6) probably squashes that idea.

[kadghar]

If the value of I is an integer between 1 and 65536 when you get to that line, it shouldnt give you a type mismatch, since it makes an implicit type conversion from a cell (actually a range) to a string. Anyway, explicit changing the type might be of help here:

[CODE=vb] If cstr(Cells(I, 1).value) <> "" Then[/CODE]
HTH