Slow loop - how to improve using arrays

[ExceL0ver]

I need to change the row reference in hundreds of 6 sets of rows of formulae because the column references are different for each row in a set and the row references will be constant for each row in a set of 6, but then increment by 1 per each set of 6 rows. If the formulae are copied down, they will be incorrect and the row references would have to manually amended.

I have therefore written the script as below to replace the row number in each set of rows so that the next incremented number is the replacement row number reference. I know this would be much more efficient if I used array references instead, but attempted and cannot implement it correctly.

Could anyone advise, please?

Thanks

Sub IncrementRowNumbers()

    Dim ws As Worksheet
    Dim i As Integer
    Dim rowNum As Integer
    Dim replaceNum As Integer

    Sheets("All Data").Activate
    
    'First line of data
    replaceNum = 6
    
    'Start at row 2
    For i = 2 To 3001 Step 6
    
    'Loop through the rows within the set of 6 rows
    For rowNum = i To i + 5
    
    'Replace "6" with the next iteration
    Range("A" & rowNum).Formula = Replace(Range("A" & rowNum).Formula, "6", replaceNum)
    Range("B" & rowNum).Formula = Replace(Range("B" & rowNum).Formula, "6", replaceNum)
    Range("C" & rowNum).Formula = Replace(Range("C" & rowNum).Formula, "6", replaceNum)
    Range("F" & rowNum).Formula = Replace(Range("F" & rowNum).Formula, "6", replaceNum)
    Range("G" & rowNum).Formula = Replace(Range("H" & rowNum).Formula, "6", replaceNum)

    Next rowNum
    
    'Increment the replacement number by 1 per each set of 6 rows
    replaceNum = replaceNum + 1
    
    Next i

End Sub

 

[Legacy 143009]

    Range("G" & rowNum).Formula = Replace(Range("H" & rowNum).Formula, "6", replaceNum)

Are you sure about this part?

 

[ExceL0ver]

That was a typo as I changed the column I needed the data to be in, there's no need to be condescending.

 

[Legacy 143009]

Ok then considering column G will be G formula:

Sub IncrementRowNumbers()

    Dim i As Integer, j As Integer
    Dim replaceNum As Integer
    Dim myArray As Variant

    myArray = Worksheets("All Data").Range("A2:G3001").Formula
    replaceNum = 6
 
    With Application.WorksheetFunction
    For i = 1 To Ubound(myArray, 1)
      For j = 1 To Ubound(myArray, 2)
        myArray(i, j) = Replace(myArray(i, j), "6", replaceNum + .Floor(i/6, 1))
      Next
    Next
    End With
    Worksheets("All Data").Range("A2").Resize(Ubound(myArray, 1), Ubound(myArray, 2)).Formula = myArray
End Sub

I wrote this without testing. I am away from my computer. I hope you can debug in case anything goes wrong.

 

[rlv01]

FWIW, I don't read @Flashbond's post as being condescending.

 

[Legacy 143009]

Infact, getting rid of Application.WorksheetFunction.Floor() method and replacing this .Floor(i/6, 1) with this Int(i/6) may speed up the things a bit more.

BTW,
@rlv01 thanks for the comment. Writing is a powerful tool. Sometimes words may sound different than they appear on the screen and vice versa. I don't mind such things. Thanks again

 

[ExceL0ver]

Ok then considering column G will be G formula:

Sub IncrementRowNumbers()

    Dim i As Integer, j As Integer
    Dim replaceNum As Integer
    Dim myArray As Variant

    myArray = Worksheets("All Data").Range("A2:G3001").Formula
    replaceNum = 6
 
    With Application.WorksheetFunction
    For i = 1 To Ubound(myArray, 1)
      For j = 1 To Ubound(myArray, 2)
        myArray(i, j) = Replace(myArray(i, j), "6", replaceNum + .Floor(i/6, 1))
      Next
    Next
    End With
    Worksheets("All Data").Range("A2").Resize(Ubound(myArray, 1), Ubound(myArray, 2)).Formula = myArray
End Sub

I wrote this without testing. I am away from my computer. I hope you can debug in case anything goes wrong.

Thank you for your assistance, Flashbond, this works in the blink of an eye and is perfect.

 

[Legacy 143009]

I am glad it did work Thanks for the feedback

 

[ExceL0ver]

I am glad it did work Thanks for the feedback

Actually, I've just realised that this isn't working quite the way I wanted after all. Whilst it does convert the numbers in the correct columns (A, B, C, F & G), it also makes changes to other columns, which is not what I want.

How do I ensure that this only targets columns A, B, C, F & G?

Thanks

 

[Legacy 143009]

Oh, I didn't realize that you are skipping D&E. Sorry.

Sub IncrementRowNumbers()
  Dim i As Integer, j As Integer
  Dim replaceNum As Integer
  Dim myArray As Variant

  myArray = Worksheets("All Data").Range("A2:G3001").Formula
  replaceNum = 6
 
  For i = 1 To Ubound(myArray, 1)
    For j = 1 To Ubound(myArray, 2)
      If j <> 4 And j <> 5 Then
        myArray(i, j) = Replace(myArray(i, j), "6", replaceNum + Int(i/6))
      End If
    Next
  Next

  Worksheets("All Data").Range("A2").Resize(Ubound(myArray, 1), Ubound(myArray, 2)).Formula = myArray
End Sub