Forum Discussion
Solved
Adding a unique customer # after every 5th row
Hi All, I've a list of 200 customers which I'd like to insert after every 5 rows. Entry beginning with ALL1221, ALL1225 is a unique customer # and the entries starting with T are item codes, the adj...
Sub items() Dim i, j As Long Range("A:C").Clear j = 1 For i = 1 To 1200 If i Mod 6 = 1 Then Cells(i, 1).Value = Cells(j, 11).Value j = j + 1 Else Select Case i Mod 6 Case Is = 2 Cells(i, 1).Value = "T40015" Case Is = 3 Cells(i, 1).Value = "T40016" Case Is = 4 Cells(i, 1).Value = "T40017" Case Is = 5 Cells(i, 1).Value = "T40018" Case Is = 0 Cells(i, 1).Value = "T40019" End Select Cells(i, 2).Value = 6 Cells(i, 3).Value = 5 End If Next i End SubIn the attached file you can click the button in cell E2 to run the macro which enters the unique customer id's from range K1:K200 in column A.
As described this far, an array formula would do the job
= LET(
n, ROWS(customerList),
items, TRANSPOSE(CHOOSECOLS(itemList, SEQUENCE(1,n,1,0))),
TOCOL(HSTACK(customerList, items))
)
Once you want a mix of formula output and input cells within a region then it becomes a non-starter.