how to copy formula without changing reference

[Guest]

thanks in advance

for instance, A1=1,A2=1, A3=A1+A2, A4=A3

now if in A6 cell, I'd liket to make a copy from A4 without changing
reference, the result should still be =A3, how could I make it ?

jiang

 

[Guest]

don't do a full copy of the cell. double click into av and copy the whole
formula. then press enter. double click into a6 and paste then press enter

 

[Tom Ogilvy]

If you change
A4: =$A$3

then if you copy A4, the cell reference won't change.

is that what you want?

 

[Guest]

thanks, ben

but I'd like to use vba to solve this problem, I know ASAP utilities could
let me do it. but I'd like to know how it realize. Thanks again.

 

[Tom Ogilvy]

For VBA code

Sub CopyFormula()
activeCell.Formula = Range("A4").Formula
End Sub

 

[Guest]

Dear Tom

Thanks a lot, actually I want to copy formula without changing reference
from seleced cells, which looks like 'cut\paste', but previous cells dont'
change, I couldn't do it in 'copy/special paste'.

I just start to work now, and will try vba code you wrote, thanks again.

Best wishes
jiang

 

[Guest]

btw, tom

what I want is,
first I could select many cells, not necessary together,

Then kind of copy formula from these cells

last I go to a new position or sheet, then 'paste' formula from selected
cells. the result is , make a new copy (for instance, the cell is exactly
same including formula), but in Excel no such function.

 

[Tom Ogilvy]

Sub CopyFormulas()
Dim rng1 as Range, rng2 as Range, i as Long
on Error Resume Next
set rng1 = Application.InputBox("Select cells to copy using mouse",type:=8)
On Error goto 0
if rng1 is nothing then
msgbox "You selected nothing"
exit sub
end if

on Error Resume Next
set rng2 = Application.InputBox("Select top cell to paste tousing
mouse",type:=8)
On Error goto 0
if rng2 is nothing then
msgbox "You selected nothing"
exit sub
end if

i = 1
for each cell in rng1
rng2(i).Formula = cell.formula
i = i + 1
Next

End Sub

If you want them spaced out like the original selections, that would take
more work.

 

[Guest]

Dear Tom,

This is exactly what I want, it's great. thanks a lot.

Best regards,
jiang