Adding Sequence numbers based on Cell value in ColumnB through VBA

satish78

Board Regular
Joined
Aug 31, 2014
Messages
215
Hi Friends,

I am trying to add sequence/following numbers in ColumnA based on cell value in ColumnB.
I been doing it manually, but its taking countless hours to go through thousands of rows.
Thought that I can get some little help here.

Attached the spreadsheet to understand more. ( Hightail Spaces )

Sheet1 Data
13 14
27 1
8 5

Sheet2 output data
13 14
13 14
14 14
15 14
16 14
17 14
18 14
19 14
20 14
21 14
22 14
23 14
24 14
25 14
26 14
27 1
8 5
9 5
10 5
11 5
12 5
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,031
Office Version
  1. 365
Platform
  1. Windows
Try this:
Sheet1 must be the active sheet when you run the macro.
VBA Code:
Sub a1159008a()

Dim i As Long, j As Long, k As Long
Dim n As Long
Dim va, vb

va = Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row)

ReDim vb(1 To 1000000, 1 To 2)

For i = 1 To UBound(va, 1)
         n = 0
            For j = 1 To va(i, 2)
                k = k + 1
                vb(k, 1) = va(i, 1) + n
                vb(k, 2) = va(i, 2)
                n = n + 1
            Next
Next

Sheets("Sheet2").Range("A1").Resize(k, 2) = vb

End Sub
 
Solution

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,031
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback. :)
 

Forum statistics

Threads
1,136,862
Messages
5,678,197
Members
419,750
Latest member
crp

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
Top