Macro to find first row blank row then write in a formula

[jbeets]

Hello Experts!

I am attempting to write a macro that will locate the first row in column “D” that is blank and then write in this formula: "=SUMIFS([WebStats_Data_Pull.xlsm]Append_Data!C14,[WebStats_Data_Pull.xlsm]Append_Data!C3,C[-1],[WebStats_Data_Pull.xlsm]Append_Data!C1,C[-3],[WebStats_Data_Pull.xlsm]Append_Data!C2,C[-2])".

I then need to copy it down to autofill to all cells below. I will need to do this for columns D, E and F. I am also getting stuck in the way that my formula currently references specific cells but the destination cell will always be different as it should be updating in the first blank row.

This is the code that I have so far…

Please Login or Register  to view this content.

I really appreciate any help you are able to provide me! Thank you!
Jbeets

[Winon]

Hello jbeets,

It is not so easy to see what you want to accomplish here:

[/QUOTE] "=SUMIFS([WebStats_Data_Pull.xlsm]Append_Data!C14,[WebStats_Data_Pull.xlsm]Append_Data!C3,C[-1],[WebStats_Data_Pull.xlsm]Append_Data!C1,C[-3],[WebStats_Data_Pull.xlsm]Append_Data!C2,C[-2])". [/QUOTE]

Are you referring to a different Workbook, or Sheet/s in the same Workbook?

The Code you have is also incorrect, unless it could be a Mac requirement, which I am not familiar with. See the quotes in red below:

Please Login or Register  to view this content.

That should actually be:

Please Login or Register  to view this content.

Maybe it would be better if you could attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.


Thank you.

[jbeets]

Thank you so much for taking the time to o read through my frustrating post. I hope that this is a little clearer...

Here is the example file with what I am trying to accomplish.

I don’t want to keep the formulas in the file since it will already be so big; I need this macro to go into the “Append_Data” tab, find the first empty cell starting in Column D and then enter the formula that I have provided. My issue is that if I type this formula in the macro, it is referencing a specific cell and would not be pointing to whatever cell is the first empty one.

This example formula might be a better choice since it is referencing specific cells such as “C2”, “A2” or “B2”. So instead of each time the formula starting at in row 2 I need it to reference column A, B or C of whatever row contains the first blank cell in column D.

Please Login or Register  to view this content.

The formula is referencing two different workbooks to calculate the desired end product. The formula works perfectly; it is just the macro portion that is causing me frustration.

Thank you again for any help you can provide.
Jbeets

[Winon]

Hello jbeets,

I must admit that your request has me baffled, and I have asked other Forum Members to see if they can help.

Let us hope a solution is on its way!

Regards.

[JBeaucaire]

One way I deal with this is by NOT adding the formula myself, but by having that formula already existing in the needed column in row 2, and then simply copying down as new data is added. Something like this to get you started:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

I don't think you are referring too the sheet name properly.

Please Login or Register  to view this content.

Don't you mean?

Please Login or Register  to view this content.

[Winon]

@JBeaucaire, and JOHN H. DAVIS,

Thank you so much Jerry, that is the way to go!

JOHN, that was exactly what I suspected as well!

We should just mind the quotes:

Please Login or Register  to view this content.

You guys are Great!

Once again, thank you for your help, time and support.

Kind Regards.