Excel VBA Loop to Convert Text to Number

Hello Experts. I am trying to write some Excel VBA to loop through a some rows and convert the text to a number.

I need the code to start with AD11:AH11 and then repeat for every third row thereafter until there's no more rows.

My below code works for AD11:AH11 but it stops there. It's not running every third row.

Any help is greatly appreciated. Thanks.

Sub TextToNumber3()

Dim rngMyRange As Range
Dim row As Integer

    With Worksheets("Cycle Time")
    
        row = 11
        
        Set rngMyRange = .Range(.Range("AD" & row), .Range("AH" & row))
          

                For Each xCell In rngMyRange
        
                    rngMyRange.NumberFormat = "0" 'Note: use "0.00" for decimal places.
                
                    xCell.Value = xCell.Value
                    
                    row = row + 3
        
                Next xCell
                
            
    End With

End Sub

Open in new window

Microsoft ExcelMicrosoft Office

Last Comment

Jorge Paulino

8/22/2022 - Mon

Jorge Paulino

7/5/2012

Try this way:

Dim rngMyRange As Range, xCell As Range
Dim row As Long, lastRow As Long

With Worksheets("Cycle Time")
    
    lastRow = Cells(Cells.Rows.Count, "AD").End(xlUp).Row

    
    For row = 11 To lastRow Step 3
    
        Set rngMyRange = .Range(.Range("AD" & x), .Range("AH" & x))
        
        For Each xCell In rngMyRange
            rngMyRange.NumberFormat = "0" 'Note: use "0.00" for decimal places.
            xCell.Value = xCell.Value 
        Next xCell

    Next

End With