VBA - Run same code for each row but increment values?

[dankello]

Hi,

With the VBA code below I need to increment certain values for each row (10 rows altogether). For example when the loop is on row #2 then values in the below code would change to:

For Each col In rng
If col.Value = Range("Milestones!D7").Value Then
Worksheets("Timeline").Range("6:6").ClearContents
Worksheets("Timeline").Range(col.Address).Offset(2, 0).Value = Range("Milestones!C7").Value
End If
Next col


This is my original code (row 1).



Private Sub Worksheet_Change(ByVal Target As Range)

' For rows 1-10 do this...
Dim rng As Range
Set rng = Application.Range("Timeline!B4:ABC4")
Dim col As Range

For Each col In rng
If col.Value = Range("Milestones!D6").Value Then
Worksheets("Timeline").Range("5:5").ClearContents
Worksheets("Timeline").Range(col.Address).Offset(1, 0).Value = Range("Milestones!C6").Value
End If
Next col
End Sub


Can anyone help with code to loop and increment each value?

Thanks,
Dan

[SamT]

Increment by what? "C7?" "C8?"
Or, increment by how much? 1? 42? 1999?

Are we supposed to check every cell in "B4:ABC4" against only "D6"

We know that you don't really want to clear all of Row 5 every time you put a new value in one of its cells. Any other subtle errors in your algorithm (https://www.merriam-webster.com/dictionary/algorithm)?

[dankello]

Hi SamT,

Sorry yes I meant to increment by 1. I have worked out a solution as below. I'm new to programming so looked into incrementing variables.



Private Sub Worksheet_Change(ByVal Target As Range)


Dim i, a, b, c, d As Integer
a = 6
b = 5
c = 2
d = 6


For i = 1 To 10


Dim rng As Range: Set rng = Application.Range("Timeline!B3:ABC3")
Dim col As Range


For Each col In rng
If col.Value = Range("Milestones!D" & a).Value Then
Worksheets("Timeline").Range(b & ":" & b).ClearContents
Worksheets("Timeline").Range(col.Address).Offset(c, 0).Value = Range("Milestones!C" & d).Value
End If
Next col

a = a + 1
b = b + 1
c = c + 1
d = d + 1

Next i


End Sub


Thanks,
Dan

[SamT]

I see, you are incrementing Row numbers.

Some tips ad tricks:

Dim A, B As Integer only declares B as an Integer, A will be a variant. You should use Dim A as Integer, B as Integer

Always use Longs for Row and Column Counters, because Integers are limited to 32K or less.

Worksheets("Timeline").Range(b & ":" & b).ClearContents
Is the same as
Worksheets("Timeline").Rows(b).ClearContents

Range(col.Address)
is the same as
col

The dot in VBA Code means "The following is a member of the preceding." Example:
Worksheets("Timeline").Rows(b).Cells(5) Means
Cells is a member of a Row, which is a member of a sheet.

When the code is running, each Dot requires the CPU to look up and reference the Parent of the Member. Using With bracketing speeds up the operation by reducing the number of such look ups. Or as we say, "reduces the dots."

Value is the default member of a Range, so we often don't need to specify it.

Applying all the above to your code, (and my personal style,) gives

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, a As Long, b As Long, c As Long, d As Long
a = 6
b = 5
c = 2
d = 6

Dim rng As Range
Dim col As Range
Dim Miles as Worksheet
Set Miles = Sheets("Milestones")

With Worksheets("Timeline")
Set rng = Range(.Range("B3"), .Cells(3, Columns.Count).End(xlToLeft))

For i = 1 To 10
For Each col In rng
If col = Miles.Range("D" & a) Then
.Rows(b).ClearContents
col.Offset(c) = Miles.Range("C" & d)
End If
Next col

a = a + 1
b = b + 1
c = c + 1
d = d + 1
Next i
End With
End Sub