Formula via Offset() exact not relative

D

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
 
J

JE McGimpsey

One way:

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

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
 
J

JE McGimpsey

There *should* have been an error message.

Try

.AutoFill .Resize(2, 1), xlFillDefault

instead.
 
D

Dennis

Still not working and no error message?

Dennis

JE McGimpsey said:
There *should* have been an error message.

Try

.AutoFill .Resize(2, 1), xlFillDefault

instead.
Click to expand...
 
Top