By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,396 Members | 2,501 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,396 IT Pros & Developers. It's quick & easy.

Nu 2 VB -- Type Mismatch Error

P: 1
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?

Expand|Select|Wrap|Line Numbers
  1. Dim Risks(20) As String
  2. Dim LH(20), Impact(20), Response(20), Trend(20), Change(20), NR(20), Del(20), XY(3, 3), XAxis(3, 3) As Integer
  3. Dim I, J, K, Flag, HI, MI, LI, RMax, Total, X, Y As Integer
  4.  
  5.  
  6. I = 10
  7. J = 1
  8. Flag = 0
  9. LI = 0
  10. MI = 0
  11. HI = 0
  12.  
  13. X = 1
  14. Y = 1
  15. While X <= 3
  16.     While Y <= 3
  17.         XY(X, Y) = 0
  18.         XAxis(X, Y) = 0
  19.         Y = Y + 1
  20.     Wend
  21.     Y = 1
  22.     X = X + 1
  23. Wend
  24.  
  25.  
  26. While Flag = 0
  27.  
  28.     Sheets("Inputs").Select
  29.  
  30.     If Cells(I, 1) <> "" Then
  31.  
  32.             Risks(J) = Cells(I, 1)
  33.             LH(J) = Cells(I, 2)
  34.             Impact(J) = Cells(I, 3)
  35.  
  36.             XY(LH(J), Impact(J)) = XY(LH(J), Impact(J)) + 1
  37.  
  38.  
  39.             Response(J) = Cells(I, 4)
  40.             Trend(J) = Cells(I, 5)
  41.             Change(J) = Cells(I, 6)
  42.             NR(J) = Cells(I, 7)
  43.             Del(J) = Cells(I, 8)
  44.  
  45.             I = I + 1
  46.             J = J + 1
  47.                                         Else
  48.  
  49.             Flag = 1
  50.     End If
  51. Wend
  52.  
  53. Total = J - 1
  54.  
  55.  
  56. RMax = 0
  57.  
  58. X = 1
  59. Y = 1
  60.  
  61. While X <= 3
  62.     While Y <= 3
  63.         If XY(X, Y) > RMax Then
  64.             RMax = XY(X, Y)
  65.         End If
Feb 25 '08 #1
Share this Question
Share on Google+
2 Replies


Expert 5K+
P: 8,434
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.
Feb 25 '08 #2

kadghar
Expert 100+
P: 1,295
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:

Expand|Select|Wrap|Line Numbers
  1.     If cstr(Cells(I, 1).value) <> "" Then
HTH
Feb 26 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.