Forum Discussion

Gurpreet20's avatar
Gurpreet20
Copper Contributor
Apr 01, 2024
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...
excel
Formulas and Functions
Macros and VBA
office 365
  • OliverScheurich's avatar
    OliverScheurich
    Apr 01, 2024

    Gurpreet20 

    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 Sub

    In 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.

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Apr 30, 2024

Gurpreet20 

Hi Gurpreet, i hope you're doing well too.

 

In your last message you wrote:

 

Could the values be automatically be updated in columns A through D when I enter 50 stores numbers in column K and their corresponding store classification in column J?

 

Does this mean that you want to enter store numbers in column K and the classification in column J and have the results updated without running the macro by clicking the button which is now in cell W4? This could be possible with a worksheet change event. Does the code return the intended result if you run it by clicking cell W4? If so i could try to write the code for the worksheet change event.

 

In sheets StoreA, StoreB, StoreC and StoreD i've removed the headers which simplifies the code considerably. I hope this is ok with you.

 

Gurpreet20's avatar
Gurpreet20
Copper Contributor
May 02, 2024

OliverScheurich 

Hi Oliver,

Yes, the macro in cell W4 is returning the intended result. No changes required. I ran a couple of tests and everything is working as it should. This is no short of "WOW" :stareyes:  A big thanks to you for this code :happyface:

I'm a newbie to VBA, and would like to learn and get better at (at least basic) coding. Any resources you'd recommend?

 

Resources