Tough one - Cell referencing in Excel/VBA

[warrenshooter]

I have two sheets (Sheet1 and Sheet2)

In cell A5 in Sheet1 I have a formula "=Average(A3,A4)"

I would like to copy this formula to cell A5 in Sheet2 but have the formula
still reference Sheet1. In cell A5 in Sheet2 the formula should be
"=Average(Sheet1!A3,Sheet1!A4)"

If Sheet1!A5 was originally "=Average(Sheet1!A3,Sheet1!A4)" then there is no
problem.

Would love to hear the answer. It must be something simple.

Warren

 

[JLGWhiz]

Seemed to work ok with Copy>PasteSpecial>PasteLink

 

[Jim Cone]

Another way...
Select both sheets before entering the formula.
--
Jim Cone
Portland, Oregon USA


"warrenshooter" <[email protected]>
wrote in message
I have two sheets (Sheet1 and Sheet2)

In cell A5 in Sheet1 I have a formula "=Average(A3,A4)"

I would like to copy this formula to cell A5 in Sheet2 but have the formula
still reference Sheet1. In cell A5 in Sheet2 the formula should be
"=Average(Sheet1!A3,Sheet1!A4)"

If Sheet1!A5 was originally "=Average(Sheet1!A3,Sheet1!A4)" then there is no
problem.
Would love to hear the answer. It must be something simple.
Warren

 

[warrenshooter]

Hi JLGWhiz,

Myabe I missed something but following your suggestion produced
"=Sheet1!$A$5" in cell Sheet2!A5.

I was after "=Average(Sheet1!A3,Sheet1!A4)" ...... not the value or the link

Cheers

 

[warrenshooter]

Neat trick. I haven't seen that before.

Unfortunately the result in Sheet2!A5 was still "=Average(A3,A4)". I was
after ="Average(Sheet1!A3,Sheet1!A4)"

Cheers

 

[Billy Liddel]

Try this

Sub CopyFormula()
Dim shName As String
Dim rngFormula As String
Dim strFormula As String
Dim iStart As Integer
Dim strLeftF As String
Dim strRightF As String
Dim c As Variant
Dim addr As String
'Get the activesheet name
shName = ActiveSheet.Name & "!"

On Error Resume Next
'get the cell formula
For Each c In ActiveSheet.UsedRange

If Not c.HasFormula Then
'do nothing
Else

addr = c.Address
rngFormula = c.Formula
'change the formula
iStart = WorksheetFunction.Find("(", rngFormula)
strLeftF = Left(rngFormula, iStart)
strRightF = Right(rngFormula, Len(rngFormula) - iStart)
strFormula = strLeftF & shName & strRightF

With Sheets("Sheet2").Range(addr)
.Formula = strFormula
End With
Debug.Print c & vbTab & c & vbTab & strFormula
End If



Next c
End Sub


HTH
Peter

 

[JLGWhiz]

I do not believe the logic that Excel uses will allow that result when
copying. Copy will either capture the value or the source of the value. I
don't know how you could tell it to pick up the precedents for the value as
part of the copy process.

 

[Jim Cone]

My result in Sheet2 was...
=AVERAGE(Sheet1!A3:A4)
--
Jim Cone
Portland, Oregon USA



"warrenshooter" <[email protected]>
wrote in message
Neat trick. I haven't seen that before.
Unfortunately the result in Sheet2!A5 was still "=Average(A3,A4)". I was
after ="Average(Sheet1!A3,Sheet1!A4)"
Cheers

 

[warrenshooter]

Hi Everyone,

Thanks very much for all the responses. Some great ideas but didn't solve
the problem.

I actually start with the case below and need to make a copy of the formula
into another sheet. It seems I need to 'decorate' the original references
with the activesheets name. So some code will create "=Average(A3:A4)" to
"=Average(Sheet!A3:A4).....it gets complicated when the original formula
becomes "=Average(A3,A4,A5:A6)" or something similar.

The results I want are the same as cut/paste without dependent formulas also
changing their references.

Cheers and thanks again for all the replies.

 

[warrenshooter]

Solve the problem.

Not the greatest code in the world but does the job

Moves the src formula to a temp cell on the source sheet.
Cut and Pastes it to a temp cell on dest cheet (this performs the source
sheet referencing)
Copy and Pastes the temp cell on the dest sheet to the dest cell (this
updates the relative cell references)

Cheers and thanks for everyones help.

Sub CopyFormula()
Dim srcformulaCell As Range
Dim destformulaCell As Range
Dim tempCell_WS1 As Range
Dim tempCell_WS2 As Range

Set srcformulaCell = Worksheets("Sheet1").Range("A5")
Set tempCell_WS1 = Worksheets("Sheet1").Range("Z1")
Set tempCell_WS2 = Worksheets("Sheet2").Range("Z1")
Set destformulaCell = Worksheets("Sheet2").Range("A5")

tempCell_WS1.Formula = srcformulaCell.Formula
tempCell_WS1.Cut
Worksheets("Sheet1").Paste Destination:=tempCell_WS2
Worksheets("Sheet2").Range("Z1").Cut
Worksheets("Sheet2").Paste Destination:=destformulaCell
End Sub

 

[Per Jessen]

Select both sheets as Jim suggested, then enter this formula:

=Average(Sheet1!A3:A4)

Regards,
Per