VBA code for a Macro button to add sequential numbers on individual rows, depending on cell value

[marshen]

Hi all.

I'm looking some VBA code that I can assign to a button or a formula if that will work

I have list headers which are copied to cells C24:C36, then I'm assigning values to cells D24:36, these values represent the amount of sequential numbers I need adding.

Basically, I want to push a button and then the code will look at cells D24:D36 and then whatever value is in those cells, I need that amount of sequential numbers adding to specific rows.


As an example

D24 = 5
D25 = 1
D26 = 0
D27 = 2
D28 = 1
D29 = 0
D30 = 1
D31 = 1
D32 = 2
D33 = 2
D34 = 0
D35 = 0
D36 = 0

When the button is pressed I then want numbers above adding to individual rows as shown below

D24 = 5 then 1 to 5 adding on individual rows starting in B4
D25 = 1 then 1 adding to D4
D26 = 0 nothing added to F4
D27 = 2 then 1 to 2 adding on individual rows starting in H4
D28 = 1 then 1 adding to J4
D29 = 0 nothing added to L4
D30 = 1 then 1 adding to N4
D31 = 1 then 1 adding to P4
D32 = 2 then 1 to 2 adding on individual rows starting in R4
D33 = 2 then 1 to 2 adding on individual rows starting in T4
D34 = 0 nothing added to V4
D35 = 0 nothing added to X4
D36 = 0 nothing added to Z4


I've added a screenshot below to show the logic.


Any help would be appreciated

Thanks
Mark

 

[rpaulson]

try this on a copy of your file.

Sub do_it()


c = 2 'start in column 2

For r = 24 To 36 'adjust as needed
cnt = Cells(r, "D")

If cnt > 0 Then
    For n = 1 To cnt
        Cells(3 + n, c) = n
    Next n
End If

c = c + 2
Next r

End Sub

hth,
Ross

 

[marshen]

try this on a copy of your file.

Sub do_it()


c = 2 'start in column 2

For r = 24 To 36 'adjust as needed
cnt = Cells(r, "D")

If cnt > 0 Then
    For n = 1 To cnt
        Cells(3 + n, c) = n
    Next n
End If

c = c + 2
Next r

End Sub

hth,
Ross

Thanks for your help