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

 

[ExceL0ver]

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

Much better, this works as it should now, Thank you so much for the speedy reply and solution!

 

[ExceL0ver]

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

No worries, this works as it should now, thank you for the speedy reply and solution!

 

[Legacy 143009]

Thanks for the follow up

 

[ExceL0ver]

I need a little more help if you could, please.

If I wanted to offset the row to the one below so that the numbers being replaced remain as 6 in rows 2-7 and then increment by 1 for every 6 rows thereafter (rows 8-13, 14-19 etc.,), what do I need to modify in the code to achieve this?

 

[Legacy 143009]

Hi,
Modify this line according to where you want to start. You can start from A2 row or A3 row.

  myArray = Worksheets("All Data").Range("A2:G3001").Formula

For the step size, this number is important: Int(i/6). It is always step size+1. For example if the desired step size is 5, it is 5+1=6. If you want to make step size 6, then modify it like Int(i/7)

Since your range size always stays the same, this code will be more maintainable:

Sub IncrementRowNumbers()
  Dim i As Integer, j As Integer
  Dim replaceNum As Integer
  Dim myArray As Variant
  Dim myRange As Range
  Set myRange = Worksheets("All Data").Range("A2:G3001")

  myArray = myRange.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/7))
      End If
    Next
  Next

  myRange.Formula = myArray
End Sub

 

[ExceL0ver]

Thank you for the guidance. This code now correctly keeps the number to be replaced as 6 in rows 2-7, but incorrectly changes the replacement number thereafter (it increments by 1 in each set, but also increments the set of rows and changes the number in the next 7 rows, then the next 8, etc,.

The first row contains headers which is why I started at row 2. What I also wanted was for the code to retain the number to be replaced as 6 in the 1st set of 6 rows (2-7), and increment the number by 1 in every set of 6 rows thereafter (change 6 to 7 in rows 8-13, change 6 to 8 in rows 14-19, etc,.)

How would I do that?

Thanks

 

[ExceL0ver]

Thank you for the guidance. This code now correctly keeps the number to be replaced as 6 in rows 2-7, but incorrectly changes the replacement number thereafter (it increments by 1 in each set, but also increments the set of rows and changes the number in the next 7 rows, then the next 8, etc,.

The first row contains headers which is why I started at row 2. What I also wanted was for the code to retain the number to be replaced as 6 in the 1st set of 6 rows (2-7), and increment the number by 1 in every set of 6 rows thereafter (change 6 to 7 in rows 8-13, change 6 to 8 in rows 14-19, etc,.)

How would I do that?

Thanks

Never mind, I've realised that I needed to change the range to A3 in both parts of the code below not just the first instance, to get the desired result, thank you for your help

  myArray = Worksheets("All Data").Range("A3:G30").Formula

  Worksheets("All Data").Range("A3").Resize(UBound(myArray, 1), UBound(myArray, 2)).Formula = myArray

 

[Legacy 143009]

You are right. I do the math wrong. You won't increase the right hand side of the division ("/"). You must decrase left hand side:

Sub IncrementRowNumbers()
  Dim i As Integer, j As Integer
  Dim replaceNum As Integer
  Dim myArray As Variant
  Dim myRange As Range
  Set myRange = Worksheets("All Data").Range("A2:G3001")
  
  myArray = myRange.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 - 1) / 6))
      End If
    Next
  Next

  myRange.Formula = myArray
End Sub

 

[ExceL0ver]

You are right. I do the math wrong. You won't increase the right hand side of the division ("/"). You must decrase left hand side:

Sub IncrementRowNumbers()
  Dim i As Integer, j As Integer
  Dim replaceNum As Integer
  Dim myArray As Variant
  Dim myRange As Range
  Set myRange = Worksheets("All Data").Range("A2:G3001")
 
  myArray = myRange.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 - 1) / 6))
      End If
    Next
  Next

  myRange.Formula = myArray
End Sub

Perfect, this also works as it should now!

 

[ExceL0ver]

You are right. I do the math wrong. You won't increase the right hand side of the division ("/"). You must decrase left hand side:

Sub IncrementRowNumbers()
  Dim i As Integer, j As Integer
  Dim replaceNum As Integer
  Dim myArray As Variant
  Dim myRange As Range
  Set myRange = Worksheets("All Data").Range("A2:G3001")
 
  myArray = myRange.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 - 1) / 6))
      End If
    Next
  Next

  myRange.Formula = myArray
End Sub

I hope you can help me with this again. I have added another column (H) that contains the formula =IF(ISNUMBER($F2),IF('GLD'!$AJ$6=0, "", 'GLD'!$AJ$6),"")

I have changed the range in the script above from ("A2:G3001") to ("A2:H3001") as I need the script to apply the same amendments this column too. However, I need to differentiate between which instances of the number 6 should be changed by the script.

The number 2 in the IF(ISNUMBER($F2) part of the fomula increments every row, which I want to remain as is. I only want the script to make changes to the number 6 in the IF('GLD'!$AJ$6=0, "", 'GLD'!$AJ$6),"") part of the formula.

What changes should I make to the script above for this to work?

Thanks