Help with filling repeated sequential numbers.

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
Office Version
  1. 2010
Hello,

I am looking a solution for filling a repeated sequential numbers that has 4 criteria
1- Fill Total Row "criteria in cell B1"
2- Starting number "criteria in cell B2"
3- Fill repeats sequence "criteria in cell B3"
4- Add in previous number "criteria in cell B4"

Note: first fill series 1 to 84 total numbers in column B starting with B6 to down than start filling column C starting with C6 to down

Example attached

Book1
ABCD
1Fill Total Row84
2Starting Num253
3Repeats Sequence13
4Add In Previous2
5
61253
72253
83253
94253
105253
116253
127253
138253
149253
1510253
1611253
1712253
1813253
1914255
2015255
2116255
2217255
2318255
2419255
2520255
2621255
2722255
2823255
2924255
3025255
3126255
3227257
3328257
3429257
3530257
3631257
3732257
3833257
3934257
4035257
4136257
4237257
4338257
4439257
4540259
4641259
4742259
4843259
4944259
5045259
5146259
5247259
5348259
5449259
5550259
5651259
5752259
5853261
5954261
6055261
6156261
6257261
6358261
6459261
6560261
6661261
6762261
6863261
6964261
7065261
7166263
7267263
7368263
7469263
7570263
7671263
7772263
7873263
7974263
8075263
8176263
8277263
8378263
8479265
8580265
8681265
8782265
8883265
8984265
90
91
92
93
Sheet1


Thank you all
Excel 2000
Regards,
Moti
 
Last edited:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Try this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG08Jul02
[COLOR="Navy"]Dim[/COLOR] St [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Seq [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nAdd [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    num = Range("B1")
    St = Range("B2")
    Seq = Range("B3")
    nAdd = Range("B4")
[COLOR="Navy"]For[/COLOR] n = 1 To num
     Cells(n + 5, 2) = n
    Cells(n + 5, 3) = St
    St = St + IIf(n Mod Seq = 0, nAdd, 0)
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Try this:-
Code:
[COLOR=navy]Sub[/COLOR] MG08Jul02
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Mick, This worked very well</SPAN></SPAN>

Thanks for all your help!
</SPAN></SPAN>

Regards,
</SPAN>
Moti
</SPAN></SPAN>

 
Upvote 0
Hello Mick, may if ask can you add that repeated sequences in column C have 2 different colours as per example Post#1 Please.</SPAN></SPAN>

Regards,
</SPAN>
Moti
</SPAN></SPAN>
 
Upvote 0
Try this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG08Jul52
[COLOR="Navy"]Dim[/COLOR] St [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Seq [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nAdd [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
    num = Range("B1")
    St = Range("B2")
    Seq = Range("B3")
    nAdd = Range("B4")
Col = vbRed
[COLOR="Navy"]For[/COLOR] n = 1 To num
    Cells(n + 5, 2) = n
    [COLOR="Navy"]With[/COLOR] Cells(n + 5, 3)
        .Value = St
        .Font.Color = Col
        .Font.Bold = True
        .Font.Size = 11
        .HorizontalAlignment = xlCenter
    [COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]If[/COLOR] n Mod Seq = 0 [COLOR="Navy"]Then[/COLOR]
        St = St + nAdd
        Col = IIf(Col = vbRed, vbBlack, vbRed)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Try this:-
Code:
[COLOR=Navy]Sub[/COLOR] MG08Jul52
[COLOR=Navy]Dim[/COLOR] St [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] num [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Seq [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] nAdd [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Col [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
     
[COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Thank you Mick, for adding 2 different colours in the repeated sequence, this makes better view for change of the sequence.

Sorry for giving you a trouble to rewriting code again.
I appreciate your valuable time and assistance
:beerchug:

Good day

Regards,
Moti
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top