Formula within a Macro not working as expected.
 

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.

Formula within a Macro not working as expected.
 

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


"Pank" wrote in message
...
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.

Formula within a Macro not working as expected.
 

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.


"Bernie Deitrick" wrote:

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


"Pank" wrote in message
...
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.