Keep getting "Type mismatch"

[Philosophaie]

'I do not understand this error. I first tried without the if statement and
10: that was not the problem. I tried changing the dim statement from 25 to
23 that did not work. I think I am initilizing it right. Just not sure.

Dim deg(25), min(25), sec(25), DEGt(25), Zodiac(25) As Double
For h = 1 To 23
If .Cells(2 + h, 3) = "" Then
DEGt(h) = ""
GoTo 10
End If
deg(h) = CDbl(.Cells(2 + h, 3))
min(h) = CDbl(.Cells(2 + h, 4))
sec(h) = CDbl(.Cells(2 + h, 5))
DEGt(h) = deg(h) + min(h) / 60 + sec(h) / 3600
Zodiac(h) = CStr(.Cells(2 + h, 2))
10:
Next h

 

[Bob Phillips]

Try this

Dim deg(1 To 25) As Double, min1 To 25) As Double, sec1 To 25) As Double
Dim DEGt(1 To 25) As Double, Zodiac(25) As Double
For h = 1 To 25
If .Cells(2 + h, 3) = "" Then
DEGt(h) = ""
GoTo 10
End If
deg(h) = CDbl(.Cells(2 + h, 3))
min(h) = CDbl(.Cells(2 + h, 4))
sec(h) = CDbl(.Cells(2 + h, 5))
DEGt(h) = deg(h) + min(h) / 60 + sec(h) / 3600
Zodiac(h) = CStr(.Cells(2 + h, 2))
10:
Next h

 

[Rick Rothstein]

Two things about your coding, neither of which should be contributing to
your problem. First, this line from your code is not doing what you think it
is doing...

Dim deg(25), min(25), sec(25), DEGt(25), Zodiac(25) As Double

Only Zodiac is being Dim'med as a Double, all the rest get Dim'med as
Variant. Unlike other languages, VB requires you to explicitly declare each
variable data type individually. So, you line of code should be this...

Dim deg(25) As Double, min(25) As Double, sec(25) As Double, etc....

Second, you If..Then block of code can be constructed without using the GoTo
statement (making it cleaner and clearer in my opinion) like this...

For h = 1 To 23
If .Cells(2 + h, 3) = "" Then
DEGt(h) = ""
Else
deg(h) = CDbl(.Cells(2 + h, 3))
min(h) = CDbl(.Cells(2 + h, 4))
sec(h) = CDbl(.Cells(2 + h, 5))
DEGt(h) = deg(h) + min(h) / 60 + sec(h) / 3600
Zodiac(h) = CStr(.Cells(2 + h, 2))
End If
Next h

Now, as I said, I really don't think either of these is contribution to your
error problem. Which line of code is registering the Type Mismatch error
(that is, which line is the debugger highlighting)? Also, what data type is
the variable "h" declared as? I also see you have "dots" in front of your
Cells calls... I assume these lines of code are within an active
With...EndWith block, correct (although, again, this shouldn't be producing
a Type Mismatch error)?

 

[Muscoby]

You have explicitly declared the variable Zodiac as a Double.
The tail end of your code, just above 10: you are trying to set it with a
String variable (CStr).
Thus, the type mismatch error.

 

[Philosophaie]

Changed Zodiac(25) to dim as string. Still gives the same error.

 

[Philosophaie]

Did both of these. Still type mismatch error

Dim Zodiac(23) as String
Dim deg(25) As Double, min(25) As Double, sec(25) As Double, etc....
DEGt="" to DEGt=0

tried substituting varient for double - same result

 

[Rick Rothstein]

I told you I didn't think those two items were the source of your problem...
I just wanted you to be aware of them for your future programming efforts.

However.... you didn't answer any of the questions I asked. Here they are
again...

1. Which line of code is registering the Type Mismatch error (that is, which
line is the debugger highlighting)?

2. What data type is the variable "h" declared as?

3. I also see you have "dots" in front of your Cells calls... I assume
these lines of code are within an active With...EndWith block, correct
(although, again, this shouldn't be producing a Type Mismatch error)?

 

[Philosophaie]

1-Type Mismatch on:

deg(h) = CDbl(.Cells(2 + h, 3))

2- h is an integer

3- With Sheets(1)...End With

 

[Muscoby]

I can simulate your error only if the statement deg(h) = CDbl(.Cells(2+h,3))
cannot be resolved. In this case because of text characters in the cell.

Check the values in the cells. Is it possible there are text strings in
these cells such as a degree sign or a temperature scale (C, F, K)? The code
isn't wrong, it's just not able to execute because it cannot convert the
value in the cell into a double.

 

[Rick Rothstein]

I asked about the values in the cells in the first posting by this OP and he
responded with this...

deg(1)=20
min(1)=45
sec(1)=21
DEGt(1)=20.755833333

So the values appear to be numeric.

 

[Philosophaie]

I found the problem. It was in the syntax:

I first entered:
deg(h) = CDbl(.Cells(2 + h, 3))

The fix was:
deg(h) = CDbl(.Cells(h+2, 3))

Just transposing the variable to the first position and the number to the
second fixed the anomaly.

Excel did not like the variable in second position like that.

 

[Rick Rothstein]

If "h" is declared as an Integer (as you said it was), then that
transposition should not have changed anything... addition is not dependent
on order, so I am still puzzled as to why it didn't work originally.