Cdbl and Cstr with a "Type Mismatch" Error

[Philosophaie]

Sub This()
Dim A,B,C as Double
Dim S as String
With Sheets("Sheet1")
A=.cells(1,1)
B=.cells(1,2)
C=.cells(1,3)
S=.cells(1,4)
End With
End Sub

This worked one time after that a "Type mismatch" error. Tried these
solutions but none worked:

A=Cdbl(.cells(1,1))
B=Cdbl.cells(1,2))
C=Cdbl(.cells(1,3))
S=Cstr(.cells(1,4))

A=.cells(1,1).Value
B=.cells(1,2).Value
C=.cells(1,3).Value
S=.cells(1,4)

Where am I going wrong?

 

[ozgrid.com]

The error suggests that you don't have numbers in your cells and one or more
contains text.

 

[Philosophaie]

All the cells I have referenced have a number in them.

 

[ozgrid.com]

I can't reproduce the error from your code. But I have to ask, why are you
passing a number to a String variable?

 

[Philosophaie]

Here is an Excerpt from my code:

Private Sub CommandButton1_Click()
Dim d,deg(12), min(12), sec(12), DEGt(24, 2) As Double
Dim Zodiac(12) As String
Dim h, k, cc, jm As Integer
Dim constell As String
With Sheets("Sheet1")
For d = 1 To 2
For h = 1 To 12
deg(h) = (.Cells(h + 4, 3 + 4 * d))
min(h) = (.Cells(h + 4, 4 + 4 * d))
sec(h) = (.Cells(h + 4, 5 + 4 * d))
DEGt(h, dates) = (deg(h)) + (min(h)) / 60
Zodiac(h) = .Cells(h + 4, 2 + 4 * (dates - 1))
Next h
Next d
End Sub

 

[OssieMac]

I have not tested any of your code but I wonder if you have Option Base 1 set
so that the array elements number from 1 and not from zero.

Actually when dimensioning arrays it is a good idea to set the array base
when dimensioning instead of using Option Base 1 like the following.

Dim deg(1 to 12), min(1 to 12)

then for the 2 dimension arrays

DEGt(1 to 24, 1 to 2) As Double

The above method ensures that the first element number is 1. It also makes
the code transportable to any module without having to remember the Option
Base 1 and it is also possible to have both types of dimensioning in the code.

 

[OssieMac]

Having a closer look at your code I cannot come to grips with what you are
attempting to do. You have 2 nested loops which is fine if all are 2
dimension arrays but the arrays with one dimension in For h = 1 To 12 get
assigned first one value then it is over written with the second loop of For
d = 1 To 2.

You should only have one loop from 1 to 12 and the array with 2 dimensions
should be populated with 2 lines of code like the following. (I have used the
element numbers for the second dimension because I don't know what value to
use for Dates - It should be 1 or 2).

For h = 1 To 12
deg(h) = (.Cells(h + 4, 3 + 4 * d))
min(h) = (.Cells(h + 4, 4 + 4 * d))
sec(h) = (.Cells(h + 4, 5 + 4 * d))
DEGt(h, 1) = (deg(h)) + (min(h)) / 60
DEGt(h, 2) = 'Whatever it should equal
Zodiac(h) = .Cells(h + 4, 2 + 4 * (dates - 1))
Next h