range of Formula copy but not changing the content
 

I would like to ask if any way I can copy formula from range to another range
but the formula will still remain unchange as the source content. (i.e. EXCEL
will not change the related cell automatically)
i.e. copy formula from "C1" to "D1" but formula in D1 should same as C1
(C1 will NOT change its formula to sum(a2:b2)

12 12 24 =SUM(A1:B1)
342 342
523 523

Tx a lot for your kind assistance.
--
aw

range of Formula copy but not changing the content
 

Select (highlight) the formula in the formula bar and then copy.

Press Enter.

Select the new cell and paste.

Ensure that you press Enter after highlighting and copying otherwise it will
alter your formula when you select another cell.
--
Regards,

OssieMac


"aw" wrote:

I would like to ask if any way I can copy formula from range to another range
but the formula will still remain unchange as the source content. (i.e. EXCEL
will not change the related cell automatically)
i.e. copy formula from "C1" to "D1" but formula in D1 should same as C1
(C1 will NOT change its formula to sum(a2:b2)

12 12 24 =SUM(A1:B1)
342 342
523 523

Tx a lot for your kind assistance.
--
aw

range of Formula copy but not changing the content
 

Thanks for help.

Actually my actual formula should be much longer that this & should be
copied across worksheets from range (more than 1 cell) to another.

Is there another way to do? Or may you give me some hints for writting VB
for this function.

Tx a lot!!
--
aw


"OssieMac" wrote:

Select (highlight) the formula in the formula bar and then copy.

Press Enter.

Select the new cell and paste.

Ensure that you press Enter after highlighting and copying otherwise it will
alter your formula when you select another cell.
--
Regards,

OssieMac


"aw" wrote:

I would like to ask if any way I can copy formula from range to another range
but the formula will still remain unchange as the source content. (i.e. EXCEL
will not change the related cell automatically)
i.e. copy formula from "C1" to "D1" but formula in D1 should same as C1
(C1 will NOT change its formula to sum(a2:b2)

12 12 24 =SUM(A1:B1)
342 342
523 523

Tx a lot for your kind assistance.
--
aw

range of Formula copy but not changing the content
 

Select the formu;a(s) to copy.

EditReplace

What: =
With: ^^^

Replace all.

Copy and paste then reverse the process.

Couple of other methods can be found at John Walkenbach's site.

One without VBA and one with.

http://www.j-walk.com/ss/excel/usertips/tip066.htm

Here also is a macro that is not range specific.

Forgot who was the originator so no attribution.

Sub CopyFormulasExact()
Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim intColCount As Integer
Dim intRowCount As Integer

' Check that a range is selected
If Not TypeName(Selection) = "Range" Then End
' check that the range has only one area
If Not Selection.Areas.Count = 1 Then
MsgBox "Multiple Selections Not Allowed", vbExclamation
End
End If

' Assign selection to object variable
Set rngCopyFrom = Selection
If Not Selection.HasFormula Then
MsgBox "Cells do not contain formulas"
End
End If

' This is required in case cancel is clicked.
' Type 8 input box returns a range object if OK is
' clicked or False if cancel is clicked. I do not
' know of a way to test for both cases without
' using error trapping
On Error GoTo UserCancelled

' Assign object variable to user-selected cell
Set rngCopyTo = Application.InputBox( _
prompt:="Select the UPPER LEFT CELL of the " _
& "range to which you wish to paste", _
Title:="Copy Range Formulae", Type:=8).Cells(1, 1)

On Error GoTo 0

' Loop through source range assigning any formulae found
' to the equivalent cell of the destination range.
For intColCount = 1 To rngCopyFrom.Columns.Count
For intRowCount = 1 To rngCopyFrom.Rows.Count
If rngCopyFrom.Cells(intRowCount, _
intColCount).HasFormula Then
rngCopyTo.Offset(intRowCount - 1, _
intColCount - 1).Formula = _
rngCopyFrom.Cells(intRowCount, _
intColCount).Formula
End If
Next intRowCount
Next intColCount

UserCancelled:
End Sub


Gord Dibben MS Excel MVP


On Mon, 19 Nov 2007 19:23:01 -0800, aw wrote:

Thanks for help.

Actually my actual formula should be much longer that this & should be
copied across worksheets from range (more than 1 cell) to another.

Is there another way to do? Or may you give me some hints for writting VB
for this function.

Tx a lot!!

range of Formula copy but not changing the content
 

Dear all,

It's work great. Thanks a lot!!
--
aw


"Gord Dibben" wrote:

Select the formu;a(s) to copy.

EditReplace

What: =
With: ^^^

Replace all.

Copy and paste then reverse the process.

Couple of other methods can be found at John Walkenbach's site.

One without VBA and one with.

http://www.j-walk.com/ss/excel/usertips/tip066.htm

Here also is a macro that is not range specific.

Forgot who was the originator so no attribution.

Sub CopyFormulasExact()
Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim intColCount As Integer
Dim intRowCount As Integer

' Check that a range is selected
If Not TypeName(Selection) = "Range" Then End
' check that the range has only one area
If Not Selection.Areas.Count = 1 Then
MsgBox "Multiple Selections Not Allowed", vbExclamation
End
End If

' Assign selection to object variable
Set rngCopyFrom = Selection
If Not Selection.HasFormula Then
MsgBox "Cells do not contain formulas"
End
End If

' This is required in case cancel is clicked.
' Type 8 input box returns a range object if OK is
' clicked or False if cancel is clicked. I do not
' know of a way to test for both cases without
' using error trapping
On Error GoTo UserCancelled

' Assign object variable to user-selected cell
Set rngCopyTo = Application.InputBox( _
prompt:="Select the UPPER LEFT CELL of the " _
& "range to which you wish to paste", _
Title:="Copy Range Formulae", Type:=8).Cells(1, 1)

On Error GoTo 0

' Loop through source range assigning any formulae found
' to the equivalent cell of the destination range.
For intColCount = 1 To rngCopyFrom.Columns.Count
For intRowCount = 1 To rngCopyFrom.Rows.Count
If rngCopyFrom.Cells(intRowCount, _
intColCount).HasFormula Then
rngCopyTo.Offset(intRowCount - 1, _
intColCount - 1).Formula = _
rngCopyFrom.Cells(intRowCount, _
intColCount).Formula
End If
Next intRowCount
Next intColCount

UserCancelled:
End Sub


Gord Dibben MS Excel MVP


On Mon, 19 Nov 2007 19:23:01 -0800, aw wrote:

Thanks for help.

Actually my actual formula should be much longer that this & should be
copied across worksheets from range (more than 1 cell) to another.

Is there another way to do? Or may you give me some hints for writting VB
for this function.

Tx a lot!!