Formula within a Macro not working as expected.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pank - 02 Mar 2007 12:04 GMT

I recorded a macro that inserts the following formula in cell R2:-

Range("R2").Select
ActiveCell.Formula = "=IF(ISBLANK(P2),"""",1000-P2)"

I then wanted to insert another formula in R3 and the macro recorded it as :-

Range("R3").Select
ActiveCell.Formula = "=IF(ISBLANK(P3),"""",(R2-P3))"

However when the macro has completed, and I look in cell R3 the formula
reads:-

=IF(ISBLANK(P3),"",1000-P3)

It should say =IF(ISBLANK(P3),"",(R2-P3))

What do I need to change in the macro so that the formula is as I want?

Please also note the next step in the macro is to FILL the formula from cell
R3 to R50 using:-

Selection.AutoFill Destination:=Range("R2:R50"), Type:=xlFillDefault

Any help offered will be appreciated.

Reply to this Message

Bernie Deitrick - 02 Mar 2007 14:24 GMT

Pank,

Your line:
Selection.AutoFill Destination:=Range("R2:R50"), Type:=xlFillDefault

Should be:
Selection.AutoFill Destination:=Range("R3:R50"), Type:=xlFillDefault

Though there is no need to select:

Sub NewSub()
Range("R2").Formula = "=IF(ISBLANK(P2),"""",1000-P2)"
Range("R3").Formula = "=IF(ISBLANK(P3),"""",(R2-P3))"
Range("R3").AutoFill Destination:=Range("R3:R50"), Type:=xlFillDefault
End Sub

HTH,
Bernie
MS Excel MVP

>I recorded a macro that inserts the following formula in cell R2:-
>
[quoted text clipped - 23 lines]
>
> Any help offered will be appreciated.

Reply to this Message

Pank - 07 Mar 2007 10:31 GMT

Bernie,

Firstly, Apologies for not coming back to you sooner. (Have been on holiday
for a few days). The changes you recommended worked perfectly as expected.

Secondly, Thank you for you time.

> Pank,
>
[quoted text clipped - 43 lines]
> >
> > Any help offered will be appreciated.

Reply to this Message