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

thanks, Joeu. you not only solved the problem how give me a true classroom in undertanding vba.

I am improving the code.

Answer 3

PS.... I wrote:

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

(Sorry about the formatting above. The forum editor has a mind of its own. :sigh:)

Some other errors and comments....

1. Ostensibly, the dimensions for principal and pss should be 1 to 14, because For x = 43 to 56 iterates 14 times, not 13.
2. Ironically, that is not a problem because you mistakenly (?) set y=1 at the beginning of each iteration.

So you are only using pss(1) and principal(1), even though you set y=y+1 at the end of each iteration.

I do not know why you are using arrays, in the first place. I presume it is a prototype for some other code where that makes more sense.

If that is the case, replace y=1 with y=y+1 at the beginning of each iteration, and remove y=y+1 at the end.

3. The repeated reference to Worksheets("gru").Cells(x, 5) is inefficient. The following is better:

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

4. Note that "v" is type Variant, by default. But I prefer to declare all variables explicitly.

So I would add the statement Dim v As Variant.

Likewise, I would add Dim x As Long, y As Long.

5. Since the code is in a worksheet module, you could write Cells(x, 5) instead of Worksheets("gru").Cells(x, 5).

In a worksheet module, Cells(x, 5) is equivalent to Me.Cells(x, 5). So Cells(x, 5) is a property of Worksheets("gru"), even if the "gru" worksheet is not the active worksheet.

That said, I can understand if you would prefer to reference Worksheets("gru") explicitly, if only to avoid any confusion.

6. You might wonder why I write

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

    [....]  
End If  

End If

instead of

If Worksheets("gru").Cells(x, 5) <> "" And Worksheets("gru").Cells(x, 5) > 0 Then
[....]
End If

It is partly "defensive programming" and partly for efficiency.

But really, the And form is okay, especially if you substitute "v", as I suggest in #3 above.

---

Possible TMI....

You might wonder why we do not get a "type mismatch" error for the original comparison Worksheets("gru").Cells(x, 5) > 0, when Cells(x,5) is the null string.

After all, we do get a "type mismatch" error for the comparison "" > 0.

Here is a hint: we do not get an error for CVar("") > 0.

Cells(...).Value returns a type Variant.

And apparently, when the string operand is contained in a type Variant, VBA does the comparison like Excel does (*), at least to the extent that all text is considered greater than any numeric value.

(* But when comparing two numeric values, VBA compares the full precision of their binary values. In contrast, Excel rounds each operand internally to 15 significant digits, just for the purpose of the comparison. That applies only to the comparison operators "=", "<>", etc, not to matches and lookups, for example.)

Answer 4

I can.

I only expect to be undertandable, because the keywords and names are in portuguese.

https://prfbr-my.sharepoint.com/:x:/g/personal/paulo_vitor_prf_gov_br/EWq6UPD26zdMqf8r9Ebew74Bdbu28hQ-S6XETXLRbY2xvA?e=Rn4v4y