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

[aba264]

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 table	h1	h2	h3
x	a	b	c
VBA generated result table	h1	h2	h3
x	a	b	c
x+1	a1	b1	c1
x+2	a2	b2	c2
x+n	an	bn	cn

<tbody>
</tbody>

 

[VBA Geek]

if you want to keep your records right below as in your example and your data goes from A1:I2 then

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]

Awesome!!
One more question, how would you provide an increment, a range and automate it, so it populates for all values in the range?

 

[VBA Geek]

hi, I do not get the question, can you make an example?

 

[aba264]

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?

 

[VBA Geek]

You can, you need to use a simple straight "For.. Next" Loop.

if you are interested in learning how it works then watch

Excel 2010 VBA Tutorial 20 - FOR Loops - YouTube

let me know if any questions