how to copy formula without changing reference
 

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

how to copy formula without changing reference
 

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

"jiang" wrote:

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

how to copy formula without changing reference
 

If you change
A4: =$A$3

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

is that what you want?

--
Regards,
Tom Ogilvy

"jiang" wrote in message
...
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

how to copy formula without changing reference
 

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.

how to copy formula without changing reference
 

For VBA code

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

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
If you change
A4: =$A$3

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

is that what you want?

--
Regards,
Tom Ogilvy

"jiang" wrote in message
...
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

how to copy formula without changing reference
 

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

"Tom Ogilvy" wrote:

For VBA code

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

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
If you change
A4: =$A$3

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

is that what you want?

--
Regards,
Tom Ogilvy

"jiang" wrote in message
...
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

how to copy formula without changing reference
 

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.

how to copy formula without changing reference
 

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.

--
Regards,
Tom Ogilvy



"jiang" wrote in message
...
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.

how to copy formula without changing reference
 

Dear Tom,

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

Best regards,
jiang

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy



"jiang" wrote in message
...
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.