Fixing a cell reference in a formula during a macro VBA script

[D.Lovell]

I have a template workbook and a macro that finds lines of data in a source workbook and copies parts of it to the template workbook. It then deletes left-over blank rows, leaving a summary section just below the last row. There is a formula in column C of the summary section that gets messed up slightly by the deleted rows, specifically a cell reference within the formula that refers to data on another sheet within the same workbook.

On the template the original formula is in cell C1667 of a sheet entitled "2014" and reads...

Please Login or Register  to view this content.

After the macro is run, lets say that for argument sake, we end up with 13 rows of data that got copied across to the template, the data exists in rows 3-13 and excess rows have been deleted. The above formula now sits in cell C16, and reads....

Please Login or Register  to view this content.

The last cell reference in the formula has not updated itself to row 13, I believe because it refers to another sheet.

I would like to add in a line of code at the end that will find which row the formula sits at in column C after excess rows have been deleted, and correct it, so that for this example, it would end up being this....

Please Login or Register  to view this content.

The formula will always be in columns C but what row it ends up on will vary.

Can anyone please suggest a way to fix this?

[Norie]

How are you putting the formula in the cell?

[D.Lovell]

If it's possible to modify the formula itself so that it does update correctly according to the number of rows above it that get deleted then that would be even better, but I have a feeling it can't be done that way, in which case I need to add VBA code into a macro to find this line and correct the last cell reference in the formula, after a varying number of rows above it have been deleted.

Any help would be much appreciated, many thanks

[D.Lovell]

Hi Norie,

The formula is already in the cell to start with, at C1667, the workbook is a pre-arranged template having data dropped onto it by a macro which finished off by deleting the excess blank rows, as a result the cell containing the formula moves up by however many rows are deleted.

[Norie]

What you could do is use R1C1 notation for the formula.

Please Login or Register  to view this content.

This will put this formula,

Please Login or Register  to view this content.

in C16 of the active sheet.

[D.Lovell]

Right I see, yes that solves part of it

Now, rather than doing this specifically in cell C16, I need to specify a variable row number that will reflect the number of rows of data that exist above it. Definitely column C, but one row below however many rows of data there are on the sheet.

Are you able to suggest how your line of code could be written to do that?

[Norie]

I thought the formula was always going to be 3 rows below the data.

If it is then you would be able to use the formula I posted without change.

Can you post your current code?

[D.Lovell]

Actually yes you are right, sorry about that, it is going to always be 3 rows below the last line of data. The amount of rows of data may vary.

In this line of code...

Please Login or Register  to view this content.

I think all I need to do is replace ("C16") with a count of how many rows of data there are on the sheet plus 3, or something like that.

The example at the end of this thread is what I want to add this code into.....

http://www.excelforum.com/excel-prog...ml#post3675984

[Norie]

I only hard-coded C16 as it's the cell you mentioned in your post.

Of course it'll need to be replaced to put the formula in the right cell based on the data.

However since I don't know what code you are currently using it's hard to suggest what to replace it with.

It could be something like this.

Please Login or Register  to view this content.

[D.Lovell]

This is exactly what I needed, thanks for your help