macOS Mac Excel VB Error - Type Mismatch - Variant

[umopapisdn]

Hi, I have this code working fine on Windows Excel. AFAIK it should work fine with Mac Excel 2004. Currently it throws up and error saying type mismatch on line 11 .
Does anyone know what is going on?

1. Public Function NumPPLNumeric() As Integer
2. Dim Y As Variant
3. Dim intRow As Integer
4. Dim intCol As Integer
5.
6. intRow = 1
7. intCol = 1
8.
9. Y = Range("weight").Offset(intRow, -3).Value
10.
11. Do While (Y > 0 And Y <> " ")
12. NumPPLNumeric = NumPPLNumeric + 1
13. intRow = intRow + 1
14. Y = Range("weight").Offset(intRow, -3).Value
15. Loop
16.
17. End Function

 

[mags631]

Can't explain why it works differently (other then the obvious, VBA must be implemented differently between the two versions). My guess is that there is no short-circuit evaluation in VBA.

So, a safer implementation might be:

Do While True
   If IsText(Y) Then
      If Trim(Y) = "" Then
         Goto Done
      End If
   ElseIf IsNumeric(Y) Then
       If Y <= 0 Then
          Goto Done
       End If
   End If
   ... Your lines 12-14
End Loop
Done:

End Function

I did not test the above -- apologies in advance if it did not help.