VBA - Updating Dynamic Line Chart & Inserting Dynamic Formula

Hello,

So I have been spinning my wheels on this quite a bit and am turning to you guys for some help.

Here is the scenario:

I have a worksheet that contains multiple line charts. Above each chart is a small table that contains the values for the X & Y axes (Date is X, Value is Y) that is formatted as shown in the attached sample picture.

1. Every time the macro runs a new Date and new Value will be generated in the table. How can I use vba to dynamically link the table to the chart and update the data series (Date is X axis, Value is Y axis)?. For example, when 4/30/2014 runs Chart1 should contain the new date and associated value.

2. The 3rd line in the table reflects the percentage change each month between the values. How can the % change formula be inserted into the new column each time a new Date & Value is populated? For example, if the macro runs and produces 4/30/2014 as a Date, and 100 as a Value, how can the formula that produces "-15%" be added in dynamically?


Thanks very much in advance for any help


chart1.png

Hi all,

Any help would be greatly appreciated. Am still trying to crack this since the original post. All I need to be able to do is dynamically update the range in the chart based on the table (above the chart) and populate the formula in the % change row each time a new date & value are added.

Thank you again for any insight.

I figured out how to program the dynamic ranges, last piece I need help with is being able to populate a formula across a row until the cell above it is empty. In this example, the formula would always start in D6, and then go to E6, F6, G6, and so on until the cell above it is blank (e.g. H5).

Any help is appreciated, thanks.