runtime error - mismatch types

Question

Guys, I have problems with the code below:

basically, the fifth column of the gru worksheet brings currency values that may or may not be filled in between lines 43 to 56.

however, I need to divide these values into 80% and 20% to put elsewhere.

I can't, because the error incompatible types always appear in the line I try to apply the calculation (rate(y) = Worksheets("gru").Cells(x, 5).Value * 0.2)

Sub divideValues_Rate()

Dim main(13) As Currency

Dim rate(13) As Currency

For x = 43 To 56

 If Worksheets("gru").Cells(x, 5) > 0 Then 

     y = 0 

     rate(y) = Worksheets("gru").Cells(x, 5).Value \* 0.2 

     ' rate(y) = Worksheets("gru").Cells(x, 5).Value / 10 \* 2 

     main(y) = Worksheets("gru").Cells(x, 5).Value - rate(y) 

     Debug.Print rate(y) 

     Debug.Print main(y) 

     y = y + 1 

 End If 

next x

End Sub

Answer 1

Paulo wrote:

the error incompatible types always appear

Not "always". But it does appear for x=45 to 56

---

could be the data in the sheet? I got that of formula =SEERRO(PROCV(C43;$A$17:$F$40;5;FALSO);"").

howerer, the format is Currency in the cell

Exactly right! You are your own best debugger. 😄

The format of the cell has nothing to do with it.

It is the fact that E45 through E56 contain the null string, which is the value-if-error result of your IFERROR function.

---

maybe On Error Resume Next be my last resource. where I put that?

That would not help in this case.

But I find a different On Error statement to be useful, as demonstrated below.

Your actual code is as follows, with some improvements highlighted in red.

Sub dividirValores_Cedidos()
Dim principal(1 To 13) As Currency
Dim pss(1 To 13) As Currency
On Error GoTo oops
For x = 43 To 56
If Worksheets("gru").Cells(x, 5) > 0 Then
y = 1
pss(y) = Worksheets("gru").Cells(x, 5).Value * 0.2
principal(y) = Worksheets("gru").Cells(x, 5).Value - pss(y)
Debug.Print x, y, pss(y), principal(y)
y = y + 1
End If
Next x
MsgBox "done"
Exit Sub

oops:
Stop ' press f8 2 times to return to faulty statement
Resume ' so we can look at the variables
End Sub

When we run the code, it stops at "oops" with the following in the Immediate Window:

43 1 450.686 1802.744
44 1 2844.558 11378.232

IMHO, that is more readable than the multiline output from individual Debug.Print statements.

More importantly, by pressing f8 twice (single-stepping), we return to the statement that causes the error, which is:

pss(y) = Worksheets("gru").Cells(x, 5).Value * 0.2

And we can look at the "variables" to see what might be causing the error.

In the Immediate Window, we see:

I typed the lines that begin with "?".

? x displays 45. So the (first) problem is with cells(45, 5). That is E45.

? cells(x,5) displays a blank line. So cells(x,5) is empty or the null string (or a string of some other non-printing characters).

? isempty(cells(x,5) displays False. So E5 is not empty. (Empty would be no value and no formula). Of course, we can see that by looking at the "gru" worksheet directly.

? cells(x,5) = "" displays True. That confirms that E5 contains the null string. Again, we could determine that by testing E5 in the worksheet directly.

? cdbl(cells(x,5)) displays the same "type mismatch" error.

So that is the problem: VBA cannot convert the null string to a number.

The following is one way to avoid the problem:

Sub dividirValores_Cedidos()
Dim principal(1 To 13) As Currency
Dim pss(1 To 13) As Currency
For x = 43 To 56
If Worksheets("gru").Cells(x, 5) <> "" Then
If Worksheets("gru").Cells(x, 5) > 0 Then
y = 1
pss(y) = Worksheets("gru").Cells(x, 5).Value * 0.2
principal(y) = Worksheets("gru").Cells(x, 5).Value - pss(y)
Debug.Print x, y, pss(y), principal(y)
y = y + 1
End If
End If
Next x
MsgBox "done"
End Sub

---

I noticed some other errors, and I have several other comments and improvements to offer.

But I must be somewhere right now. I will post a follow-up reply later.

Answer 2

Could you share a test workbook to reproduce your issue?

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

Answer 3

snow Lu, unfortunately I get the same error.

could be the data in the sheet? I got that of formula =SEERRO(PROCV(C43;$A$17:$F$40;5;FALSO);""). howerer, the format is Currency in the cell

shane, I got the results, even with error. maybe On Error Resume Next be my last resource. where I put that?

Answer 4

Try this one:

==========================

Sub divideValues_Rate()

Dim Rate(1 To 100) As Currency

Dim main(1 To 100) As Currency

For x = 43 To 56

 If Worksheets("gru").Cells(x, 5) > 0 Then 

     y = 1 

     Rate(y) = Worksheets("gru").Cells(x, 5).Value \* 0.2 

     'Rate(y) = Worksheets("gru").Cells(x, 5).Value / 10 \* 2 

     main(y) = Worksheets("gru").Cells(x, 5).Value - Rate(y) 

     Debug.Print Rate(y) 

     Debug.Print main(y) 

     y = y + 1 

 End If 

Next x

End Sub

Answer 5

Consider using an On Error Resume Next or equivalent.