Excel VBA repeat numerical series based on value in another cell or variable

paneliyadhruv

New Member
Joined
May 21, 2018
Messages
36
Dear All, First of all thank you very much.

Kindly help me on below problem.
I have 1 to 50 number in column A and from columns B to W having various numbers as below. I required output in from of series repeating based on values in from columns B to W. Details as mention below.

Serial No.varible number from column B to W or more)
ABCDEFGHIJKLMNOPQRSTUVw OutputEach row having maximum of 5000 output value
13212422222222222222222 1
23212422222222222222222 2
33 12 2 2 2 2 2 2 2 2 3
4 12 1
5 12 2
6 2 3
7 2 1
8 2 2
9 2 3
10 2 1
11 2 2
12 2 1
13 2 2
14 2 1
15 2 2
16 2 3
17 2 4
18 2 5
19 2 1
20 2 2
21 2 3
22 2 4
23 2 5
24 2 6
25 2 7
26 2 8
27 2 9
28 2 10
29 2 11
30 2 12
31 2 13
32 2 14
33 2 15
34 2 16
35 2 17
36 2 18
37 2 19
38 2 20
39 2 21
40 2 22
41 2 23
42 2 24
43 2 25
44 2 26
45 2 27
46 2 28
47 2 29
48 2 30
49 2 31
50 2 32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
1
2
1
2
1
2
1
2
so on

<colgroup><col><col span="22"><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Assuming that numerical data begins in row 2, try this in a copy of your workbook.
The results are put in column Y.
Code:
Sub Make_Sequences()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, r As Long, x As Long, y As Long
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(1)).Resize(, 23).Value
  ReDim b(1 To Rows.Count, 1 To 1)
  For k = 2 To UBound(a, 2)
    x = a(1, k)
    y = 0
    Do Until IsEmpty(a(y + 1, k))
      y = y + 1
    Loop
    For i = 1 To x
      For j = 1 To y
        r = r + 1
        b(r, 1) = j
      Next j
    Next i
  Next k
  Range("Y2").Resize(r).Value = b
End Sub
 
Upvote 0
Dear Sir,

Excellent, I am also able to extend for few more columns. Output is perfect.
Once again thank you very much.
 
Upvote 0
Dear Sir,

If my data located from cell BV2 to CR50 in sheet name "Calc" and all other previous rows may or may not contains value or other calculations. I have change macro as "a=Range("BV2", Range("BV" .... but i getting output in next available column as 1 to 50 for 77 times (total count are 3850).
Kindly suggest me to what change i have to made in above macro.

Thank you very much in advance.
 
Upvote 0
Dear Sir,

If my data located from cell BV2 to CR50 in sheet name "Calc" and all other previous rows may or may not contains value or other calculations. I have change macro as "a=Range("BV2", Range("BV" .... but i getting output in next available column as 1 to 50 for 77 times (total count are 3850).
Kindly suggest me to what change i have to made in above macro.

Thank you very much in advance.

That isn't entirely clear to me but see if this helps. In the code yo can set
- the address of the top left cell of your data BV2 in your description
- the number of columns in your data. 23 in your example of columns BV to CR
The code will put the results in the very next column. CS in this example.
Is that what you were asking for?

Rich (BB code):
Sub Make_Sequences_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, r As Long, x As Long, y As Long
  
  Const TopLeftCell As String = "BV2" '<- Data top left cell address
  Const NumberOfColumns As Long = 23  '<- Set your number of columns here

  a = Range(TopLeftCell, Cells(Rows.Count, Range(TopLeftCell).Column).End(xlUp).Offset(1)).Resize(, NumberOfColumns).Value
  ReDim b(1 To Rows.Count, 1 To 1)
  For k = 2 To UBound(a, 2)
    x = a(1, k)
    y = 0
    Do Until IsEmpty(a(y + 1, k))
      y = y + 1
    Loop
    For i = 1 To x
      For j = 1 To y
        r = r + 1
        b(r, 1) = j
      Next j
    Next i
  Next k
  Range(TopLeftCell).Offset(, NumberOfColumns).Resize(r).Value = b
End Sub
 
Upvote 0
Dear Sir,

BV2 TO BV51 having series from 1 to 50 without any calculation. while other column having output of formula "IF(ISERR(AY$2*AY5),"",(AY$2*AY5)) from BW2 to CR51.
Output in next column from 1 to 50 repeating 77 times.
 
Upvote 0
Dear Sir,

BV2 TO BV51 having series from 1 to 50 without any calculation. while other column having output of formula "IF(ISERR(AY$2*AY5),"",(AY$2*AY5)) from BW2 to CR51.
Output in next column from 1 to 50 repeating 77 times.
I'm sorry, I have no idea what you are asking. :unsure:
Is this a whole new question?
If so, please make up a much smaller example (say 10 rows and 5 columns and show the data and expected results.
Are you looking for a result by macro or formula or it doesn't matter?
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

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