Wrong code?

[Guest]

Hello,

Please can someone point out my error below.

Range("I22").Select
ActiveCell.FormulaR1C1 = "=if(" & ActiveCell.Offset(0, -1) &
"-1500)<0,0,RoundDown((" & ActiveCell.Offset(0, -1) & "-1500)/900,0)+1)"

Cheers

 

[Mark]

I think you just needed the underscore at the end of the first line.

ActiveCell.FormulaR1C1 = "=if(" & ActiveCell.Offset(0, -1) & _
"-1500)<0,0,RoundDown((" & ActiveCell.Offset(0, -1) & "-1500)/
900,0)+1)"

 

[Guest]

If the cell to the left of the activecell contains 20000, this is what it
produces:

=if(20000-1500)<0,0,RoundDown((20000-1500)/900,0)+1)

so you see you are missing a Left paren before the first 20000

ActiveCell.FormulaR1C1 = "=if((" & ActiveCell.Offset(0, -1) &
"-1500)<0,0,RoundDown((" & ActiveCell.Offset(0, -1) & "-1500)/900,0)+1)"

 

[Don Guillett]

Try

Sub OffsetActivecellformula()
ac = Range("i22").Address
Range(ac).Formula = "=ROUNDDOWN(OFFSET(" & ac & ",0,-1)" _
& "-IF(OFFSET(" & ac & ",0,-1)-1500<0,0,1500)/900,0)"
'formula below
'=ROUNDDOWN(OFFSET(L8,0,-1)-IF(OFFSET(L8,0,-1)-1500<0,0,1500)/900,0)
End Sub