Iterate number only in certain row, within sets of rows

[ExceL0ver]

I currently use the code below. I now have data in column H, so I have changed the range part to Range("A2:H3001"), as I want to iterate numbers in column H, too. However, this column contains different formulae in the rows, and I only want the script to iterate the number every 3rd row in each set of 8 rows. I think I would use "If i = 3 Then", but I'm not sure how I would apply this to only column H, as I still want the script to run as below with columns A to G.

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

Could anyone suggest how I would acheive this, please?

Thanks

 

[felixstraube]

How about?:

    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
        If i Mod 8 = 2 Then
            'Do something here
        End If
    Next

Or change the 2 to different numbers and see which one you need.

 

[ExceL0ver]

How about?:

    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
        If i Mod 8 = 2 Then
            'Do something here
        End If
    Next

Or change the 2 to different numbers and see which one you need.

Sorry, I don't follow this. Could you explain?

 

[felixstraube]

If you use

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
        If i Mod 8 = 2 Then
            myArray(i, j) = "new value " & i & " - " & j
        End If
      Next
    Next
  
    myRange.Formula = myArray
End Sub

You will get

Libro1.xlsm
	A	B	C	D	E	F	G	H
1
2
3	new value 2 - 1	new value 2 - 2	new value 2 - 3	new value 2 - 4	new value 2 - 5	new value 2 - 6	new value 2 - 7
4
5
6
7
8
9
10
11	new value 10 - 1	new value 10 - 2	new value 10 - 3	new value 10 - 4	new value 10 - 5	new value 10 - 6	new value 10 - 7
12
13
14
15
16
17
18
19	new value 18 - 1	new value 18 - 2	new value 18 - 3	new value 18 - 4	new value 18 - 5	new value 18 - 6	new value 18 - 7
20
21
22
23
24
All Data

But if you use this:

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
        If i Mod 8 = 3 Then
            myArray(i, j) = "new value " & i & " - " & j
        End If
      Next
    Next
  
    myRange.Formula = myArray
End Sub

You will get:

Libro1.xlsm
	A	B	C	D	E	F	G
1
2
3
4	new value 3 - 1	new value 3 - 2	new value 3 - 3	new value 3 - 4	new value 3 - 5	new value 3 - 6	new value 3 - 7
5
6
7
8
9
10
11
12	new value 11 - 1	new value 11 - 2	new value 11 - 3	new value 11 - 4	new value 11 - 5	new value 11 - 6	new value 11 - 7
13
14
15
16
17
18
19
20	new value 19 - 1	new value 19 - 2	new value 19 - 3	new value 19 - 4	new value 19 - 5	new value 19 - 6	new value 19 - 7
21
22
23
24
All Data

The mod operator returns the remainder after number is divided by divisor. That is you divide i by 8 and get the reminder. So if you have you numbers and your divisor you will get:

Number	Divisor	Reminder
0	8	0
1	8	1
2	8	2
3	8	3
4	8	4
5	8	5
6	8	6
7	8	7
8	8	0
9	8	1
10	8	2
11	8	3
12	8	4
13	8	5
14	8	6
15	8	7
16	8	0
17	8	1
18	8	2
19	8	3
20	8	4
21	8	5
22	8	6
23	8	7
24	8	0
25	8	1
26	8	2
27	8	3
28	8	4
29	8	5
30	8	6
31	8	7

 

[ExceL0ver]

How about?:

    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
        If i Mod 8 = 2 Then
            'Do something here
        End If
    Next

Or change the 2 to different numbers and see which one you need.

Thank you for taking the time to explain.

I have now got the If i Mod 8 = 2 Then part working for column H as a separate script but not as a whole with the original script.

 

[felixstraube]

Happy to help. Thanks for the feedback.