Insert formula and copy to a range of cells above

[mickjjuk]

Can anyone help.

I have some data being output to an excel worksheet and the number of rows
output is never the same.

I have written some code to find the last row used and then select the cell
in this row relating to a certain column. I am now having trouble insetring
a formula into this cell and copying this formula to all the cells above in
the same column.

a sample worksheet would look like this:

Tag num Jig Num Wgt A Wgt B Wgt Diff
1 100 90 95
2 101 85 95
6 130 110 150 *

The code I currenty have selects the cell * above, I then want to insert a
formula in there which subtracts Wgt A from Wgt B, then copy this formula
upwards in the cells in Wgt Diff Column to the row under the heading row.

Can anyone help?

Thanks

 

[Rick Rothstein]

This macro will find the asterisk and replace it plus all the cells above it
(up to E2) with the subtraction formula you said you wanted...

Sub ReplaceAsteriskAndFillUpwards()
Range("E2:E" & Columns("E").Find("~*").Row).Formula = "=D2-C2"
End Sub

 

[Rick Rothstein]

I just noticed... your description said "find the last cell"... are you
putting that asterisk in Column E at the last cell manually just so you can
find the last cell? If so, you don't need to do that. This macro will
automatically find the last cell (in Column A, but that is the same as the
rest of the filled in data columns) and then put the formula in the cells of
Column E automatically...

Sub ReplaceAsteriskAndFillUpwards()
Range("E2:E" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=D2-C2"
End Sub

I would point out, though, that you can eliminate the need for a macro by
just putting the following formula in E2 and then copy it down for as many
cells of Column E as you think you will ever have data in and just leave
them there... they will automatically display a difference is there is a
difference to display, otherwise it will display the empty string (which
will look like there is nothing in the cell). So, put this formula in E2

=IF(D2="","",D2-C2)

and then copy it down as far as you like.