Formula via Offset() exact not relative

[Dennis]

Excel 2003


The formula below works but it copies the formula exactly as the cell above.

myCell.Formula = myCell.Offset(-1, 0).Formula

Thus, A5 = B5+C5 and A6 =B5+C5 (if the formula above populates A6)

How can I copy a preceding Cell formula yet have it relative (A6=B6+C) ?

TIA Dennis

 

[Frank Kabel]

Hi
try
myCell.FormulaR1C1 = myCell.Offset(-1, 0).FormulaR1C1

 

[JE McGimpsey]

One way:

With myCell.Offset(-1, 0)
.AutoFill .Resize(2, 0), xlFillDefault
End With

 

[Dennis]

Thanks for your knowledge & time.

I substituted the formula in the following:


If myCell.Offset(-1, 0).HasFormula Then
' myCell.Formula = myCell.Offset(-1, 0).Formula
With myCell.Offset(-1, 0)
.AutoFill .Resize(2, 0), xlFillDefault
End With
Else
myCell.Formula = myCell.Offset(-1, 0).Value
End If

The problem is that no auto fill copy is done. No error
message is displayed. Any thoughts?

TIA Dennis

 

[JE McGimpsey]

There *should* have been an error message.

Try

.AutoFill .Resize(2, 1), xlFillDefault

instead.

 

[Dennis]

Still not working and no error message?

Dennis

There *should* have been an error message.

Try

.AutoFill .Resize(2, 1), xlFillDefault

instead.

 

[Dennis]

You are correct!!

I inserted the change into a similar macro.

My mistake

Dennis