Problems with simple counter

[MarcoPolo82]

Hallo guys,

I registerd here because I have a very simple problem, I just want to get a counter running but I am getting just an error-13 message.

My code looks like this


Sub testcounter()

Dim cell As Range
Dim a As Integer


For Each cell In Worksheets("Sheet2").Range("D1:D3500")

If cell.Value = "1" Then
a = a + 1

End If
Next

MsgBox a

End Sub


It is a simplified version of a more complicated code i wrote, but they give out the same error, every time where the If starts.
The code is based on other posts on the web and looks pretty straight forward but it does not work even in the simplest of versions like above.
Does anybody have a clue ?

[offthelip]

The code works fine all the time the cells have values in them, however if there is an error in one of the cells you will get the error 13 come up, so I suspect one of the cells has an error in it. so try this modification:


Sub testcounter()
Dim cell As Range
Dim a As Integer


For Each cell In Worksheets("Sheet2").Range("D1:D3500")

If VarType(cell.Value) <> vbError Then
If cell.Value = "1" Then
a = a + 1

End If
End If
Next

MsgBox a

End Sub

[SamT]

"1" is non-numerical.

Try

If Cell = 1 Then A = A + 1

For both numerical and non-numerical 1s (ones) use

If CDble(Cell) = 1 then ...
But that will raise an error if the cell has other than textual numbers in it such as "Some Word" so you need some error handling

For each cell....
On Error goto CellNext
If CDble(Cell) = 1 then a = a + 1
CellNext:
Err = 0 'verify this line clears errors
Next