How to perform a math equation to everything in a cell using vba not knowing how many rows I have

[Ivn68]

I have a number in column C and I need to divide by 60 and round up to nearest digit. I need to do that to all the cells in column C

Thanks for any help

 

[Joe4]

Are you wanting to replace the current value in column C, or put the result in a different column?
Are you starting on row 1, row 2, or some other row?

 

[Ivn68]

Replace the number in column C

 

[Joe4]

Try this:

Sub MyReplaceNum()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column C with data
    lr = Cells(Rows.Count, "C").End(xlUp).Row
    
'   Loop through all values in column C starting on row 1
    For r = 2 To lr
'       Check to see if number in column C
        If IsNumeric(Cells(r, "C")) Then
'           Replace value in column C
            Cells(r, "C").Value = Round(Cells(r, "C").Value / 60, 0)
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[Ivn68]

I need it to round up. Like 2.1 would round up to 3

 

[Joe4]

OK, try this then:

Sub MyReplaceNum()

    Dim lr As Long
    Dim r As Long
    Dim v As Double
    
    Application.ScreenUpdating = False
    
'   Find last row in column C with data
    lr = Cells(Rows.Count, "C").End(xlUp).Row
    
'   Loop through all values in column C starting on row 1
    For r = 2 To lr
'       Check to see if number in column C
        If IsNumeric(Cells(r, "C")) Then
            v = Cells(r, "C").Value
'           Replace value in column C
            Cells(r, "C").Value = Application.WorksheetFunction.RoundUp(v / 60, 0)
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[Ivn68]

Thanks!

 

[Joe4]

You are welcome.
Glad I was able to help!