VBA - increment base number, lookup new values and copy them to table

aba264

New Member
Joined
Feb 6, 2014
Messages
3
Hi,

I have a table with 9 columns and 2 row(including one header), where the data(a,b,c) changes based on a number(x). a,b, c are lookups/formulas based on a larger table.
I need to record the changes made when I increment that number(x). I tried to map it in the table below.

I have never used VBA. If anyone could give me some guidance, it would be great.

Thanks,

Amir

initial tableh1h2h3
xabc
VBA generated result tableh1h2h3
xabc
x+1a1b1c1
x+2a2b2c2
x+nanbncn

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
if you want to keep your records right below as in your example and your data goes from A1:I2 then

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address <> "$A$2" Then Exit Sub
    Application.EnableEvents = False
    Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Resize(, 9) = Range("A2:I2").Value
    Application.EnableEvents = True
End Sub
 

aba264

New Member
Joined
Feb 6, 2014
Messages
3
Awesome!!
One more question, how would you provide an increment, a range and automate it, so it populates for all values in the range?
 

aba264

New Member
Joined
Feb 6, 2014
Messages
3
So, I have a long list of numbers (x) which I need to increment.
Can I set the initial number(x) , an increment value (+1) and a limit (limit = 50). Then run the macro and have it automatically generate the 50 lines?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,467
Members
414,452
Latest member
Dannysamworth

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