VBA - divide value in column

Question

as I am a complete newb in VBA, I have a question.

I have a column of integers, which I want to divide by 60. The column is "X". Here's my code:

For Each element In Worksheets("parsed").Range("X1:X" & MaxRows).Cells
    element.Value = element.Value / 60
Next

But I always get "type mismatch". What I am doing wrong?

Answer 1

First, make sure that you declare element as a range. Second, remove the .cells from your loop. This then effectively says in your loop statement For each cell in the range like so:

Sub SomeProc()

Dim element As Range
Dim MaxRows As Long

With Worksheets("parsed")
    MaxRows = .Cells(.Rows.Count, "X").End(xlUp).Row
End With

For Each element In Worksheets("parsed").Range("X1:X" & MaxRows)
    If IsNumeric(element.Value) Then
        element.Value = element.Value / 60
    End If
Next

End Sub

I've made the assumption that MaxRows is intended as the last row in column X of the worksheet.

Answer 2

Dim CurRow As Long
For CurRow = 1 to MaxRows
    element = Worksheets("parsed").Range("X" & CurRow).Value / 60
    'Do something with element for example Range(Destination).Value = element
Next CurRow