Macro to write a formula with dynamic cell references

Hi there,

I have a spreadsheet that retains production information (in pivot form)for each business day on a sheet called "Daily Data Aggregation". I then plot this information on a chart and re-manipulate the data (using a VLOOKUP) into a different format on a sheet called "Chartable Data". I am trying to write a macro that will search the first row of the "Chartable Data" tab for today's date, and then when it finds it, input the following formula one cell below: =IFERROR(VLOOKUP($A2,'Daily Data Aggregation'!$B$12:$D$22,3,FALSE)/1000000,0). The trick here is that I need the red "table array" portion to change so that it is reading the today's retained production information from the "Daily Data Aggregation" tab. After this is done, I would love it it could copy the formula down just changing the row reference by one until it reaches the row that says "Total".

I tried to write two macros but I wasn't having any luck. They are below. Perhaps someone here could give me some guidance?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks,

Jordan