Copying formula from one cell to another in the same column

[QueenB4ADay]

I need help with creating a Macro/VBA Code that will start off by clearing any existing data that is in Cell I17 down to the end of the spreadsheet and to the right to the end of the spreadsheet. Copy the formula in Cell I1 into the corresponding Cell that matches the row of dates from Column H to the date that is in Cell I13. After the formula is pasted, copy the formula down. Then move over to the next column and repeat the process until it finds two blank columns. Note: All formulas in row one are unique to each column.

[Keebellah]

Hi, once you have the formula in the top wor you can use the following

Please Login or Register  to view this content.

Where S is the column and the rows

for example

in S2 I place the formula and then copy it down 41 rows

Please Login or Register  to view this content.

[QueenB4ADay]

Hi Keebellah, I appreciate the quick response. Not sure I understand what I am to do. (I am a beginner with using VBA) How do I use your code? Also please note that the dates in row 13 vary from column to column.

[Keebellah]

You question gave me the impression that you had some basic knowledge; okay, I don't know what you mean with the the dates differ per column.
My answer is only the formula and the code to copy it doen.
I suggest you attach the file, make sure there is no sensitive data, and place the formula you have or want in there.
You can also give it a try too.
Open the file and start the macro recorder, give the macro a name and then do the steps you explained.
You place the formula i the cell you want in row 13 then select the right bottom corner and copy the formula down to the last row you want the formula in.
Stop the macro recorder and the open the VBA editor and take a look at the macro.

[QueenB4ADay]

It appears I am not authorized to attach a file so you can see. The formula that is Cell I1 is =$I$8*$I$9*$I$10*$I$11*'Type Curve'!B4. The date in Cell I13 is 01/01/19 and I need to find the date (01/01/19) from the list of monthly dates in Column H starting from Cell H17, once it finds the date then copy the formula from Cell I1 into the appropriate row in Column I. The formula that is in Cell J1 is =$J$8*$J$9*$J$10*$J$11*'Type Curve'!B4. The date in Cell J13 is 09/01/18. I really appreciate your time with helping me.

[Keebellah]

Everyone can attach a file, follow the instructions

[QueenB4ADay]

Oh! my bad. Here you go.

[Keebellah]

Okay, got the file, this is much better that the explanation which gave me no clue what it looks like.
I think I can manage, I won't be adding much explanation to how I coded it but let's see if I can het it done for you for now.
Hope to do it before bedtime here, its 11:35 PM now

[Keebellah]

Okay, it's bedtime here but here's the file.
Hope it works in the 'panhandle'
Will read the results tomorrow

Just press the smiley (button) on the worksheet, the text 'Click the smiley ..' in the columns will be cleared automatically

[QueenB4ADay]

OMG!!!! You ARE the BEST!!!! You have saved me hours of manually copying and pasting whenever I have to update my report. My actual report ends in Column CP, so I changed it in the code and it works like a dream. I can't thank you enough for all your help.

[Keebellah]

You're welcome
A minor modification let's say you don't want a column to be calculated you can add a test to check if the formula is present:

I added two lines of code
one richt below the for each c

Please Login or Register  to view this content.

and one before the Next c

Please Login or Register  to view this content.

This way you can expand the report and leave the formula in row 1 blank to not include that row or while preparing your data

Please Login or Register  to view this content.

[Keebellah]

And one thing I forgot, to speed up the macro
add

Before the clearing of the data

Please Login or Register  to view this content.

right after Next c

Please Login or Register  to view this content.

[QueenB4ADay]

Awesome! Thanks once again.