I've created a loop, everything is working, but now I need to put that loop inside of another loop.
The big loop that would begin just after formatting the Z column would be:
starting at X2, complete the below for each cell in column X and end at the last cell of data.
The big loop that would begin just after formatting the Z column would be:
starting at X2, complete the below for each cell in column X and end at the last cell of data.
VBA Code:
Sub test3()
Dim rng, rng2, rng3, rng4 As Range, a, i As Long
Set rng = Range("W2")
Set rng2 = Range("AL2:AL" & Range("AL" & Rows.Count).End(xlUp).Row)
Set rng3 = Range("AM2:AM" & Range("AL" & Rows.Count).End(xlUp).Row)
Columns("Z:Z").Select
Selection.NumberFormat = "m/d/yyyy"
' Start Loop
If rng.Offset(0, 1).Value = 1 Then
Range("Z2") = "=INDEX(" & rng3.Address & ",MATCH(G2," & rng2.Address & ",0))"
rng.Offset(0, 2) = Range("Z2") - rng.Offset(0, -9)
ElseIf rng.Offset(0, 1).Value > 1 Then
a = rng.Offset(0, 1)
For i = 1 To a
rng.Offset(-1 + i, 3).FormulaArray = "=INDEX(" & rng3.Address & ", SMALL(IF(($G$2=" & rng2.Address & "), MATCH(ROW(" & rng2.Address & "), ROW(" & rng2.Address & ")), """"),ROWS($A$1:A" & i & ")))"
rng.Offset(-1 + i, 4) = rng.Offset(-1 + i, 3) - Range("$N$2")
Next i
Set rng4 = Range("AA2:AA" & Range("AA" & Rows.Count).End(xlUp).Row)
rng.Offset(0, 2).FormulaArray = "=INDEX(" & rng4.Address & ",MATCH(MIN(ABS(" & rng4.Address & "-0)),ABS(" & rng4.Address & "-0),0))"
End If
rng.Offset(0, 2).Copy
rng.Offset(0, 2).PasteSpecial Paste:=xlPasteValues
Columns("Z:AA").Clear
' End Loop
End Sub