Yes, another variable range question....

[DanaK]

I've searched & searched but can't figure this one out. I have a macro where
I need to find the last active cell (based on column A) and have placed a
formula in E at that cell. I need to copy that formula down to the last
active row based on D or G. Then I will need to copy & paste the value of E
over D. The last active cell will of course always be variable as will the
last row. I've used every bit of 'stolen' code & can't seen to modify it to
do what I need, or much of anything really. Help?

A B C D E
F G
127100 1431000 -205.74 TU17306
127100 1431000 272.77 TU17306
127100 1431000 -236.38 TU17437
127100 1431000 244.11 TU17437
858.73 =SUM(D727*-1) DO60840
448.04 DO60858
-2.14 DO60932
-11.08 DO60982
-21.00 DO61039
49.24 DO61309
-2.23 DO61337

 

[Joel]

Try soemting like this. I not sure what the formula is. I don't know what
row 727 is nor the star. I guessed what you wanted. Modify as required.

LastRow = Range("A" & Rows.Count).end(xlup).row
FirstRow = LastRow + 1
LastRow = Range("D" & Rows.Count).end(xlup).row
LastRowG = Range("G" & Rows.Count).end(xlup).row
if LastRowG > LastRow then
LastRow = LastrowG
end if

columns("D").Insert

Range("D" & FirstRow).formula = _
"=SUM(D" & (Firstrow - 1) & "-1)"
Set CopyRange = Range(Range("D" & FirstRow),Range("D" & LastRow))
Range("D" & FirstRow).copy _
Destination:=CopyRange