vba problem
 

Sub test()
Dim temp As Variant
Dim Range1 As Range
Dim cell As Range

Const big As Variant = 100000000
Set Range1 = ActiveSheet.Range("g22:g38")
For Each cell In Range1.Cells
temp = cell.Value
temp = temp / big <==== error here
cell.Value = temp
Next
MsgBox cell.Value
End Sub
The following code gives an error 13, "type miss match." Both "temp" and
"big" are variant. Why does the division throw an error 13?

If I change "temp" and "big" to double the line is acceptable;however the
previous line "temp=cell.Value" throws an error 13, "type miss match". Why?

I am missing something minor regarding the interaction between "variant" and
the other types. I have not found the "help file" to inlighten myself.

Could someone please shed some light on this?

Thanks,
James

vba problem
 

Hi,

Probably some of the cells in the range G22:G38 contains
an value that isn't numeric.

/Nicke

-----Original Message-----
Sub test()
Dim temp As Variant
Dim Range1 As Range
Dim cell As Range

Const big As Variant = 100000000
Set Range1 = ActiveSheet.Range("g22:g38")
For Each cell In Range1.Cells
temp = cell.Value
temp = temp / big <==== error here
cell.Value = temp
Next
MsgBox cell.Value
End Sub
The following code gives an error 13, "type miss
match." Both "temp" and
"big" are variant. Why does the division throw an error
13?

If I change "temp" and "big" to double the line is
acceptable;however the
previous line "temp=cell.Value" throws an error
13, "type miss match". Why?

I am missing something minor regarding the interaction
between "variant" and
the other types. I have not found the "help file" to
inlighten myself.

Could someone please shed some light on this?

Thanks,
James


.

vba problem
 

Set the variable Big to type LONG

However, your error is only thrown when the value in the
cell is non-numeric. If the cell is empty excel coerces
the value to zero. If the cell contains a textual value
eg "A" then the code will generate the error that you get.

You could test the cell value before the division with ...

If IsNumeric(Cell.Value) Then
' your code here
Else
' some error handling code here
End if



Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Sub test()
Dim temp As Variant
Dim Range1 As Range
Dim cell As Range

Const big As Variant = 100000000
Set Range1 = ActiveSheet.Range("g22:g38")
For Each cell In Range1.Cells
temp = cell.Value
temp = temp / big <==== error here
cell.Value = temp
Next
MsgBox cell.Value
End Sub
The following code gives an error 13, "type miss
match." Both "temp" and
"big" are variant. Why does the division throw an error
13?

If I change "temp" and "big" to double the line is
acceptable;however the
previous line "temp=cell.Value" throws an error
13, "type miss match". Why?

I am missing something minor regarding the interaction
between "variant" and
the other types. I have not found the "help file" to
inlighten myself.

Could someone please shed some light on this?

Thanks,
James


.