Variable increment

[G2K]

Hi All,

I am having a very small problem but i am unable to solve it since morning, i have used a variable I and assigned a value 2, unfortunately after each iteration the value of I increased with 2 instead of 1

Private Sub AddColumns()
Set WS = Sheets("DATA")
I = 0
With WS
    .Range("Y1").Value = "Deal Completed This Month"
    .Range("Z1").Value = "Opportunities Persued This Month"
    .Range("AA1").Value = "Opportunities Persued This Year"
    .Range("AB1").Value = "Inactive"
    .Range("AC1").Value = "Close Date Category"
    LR = .Range("B65536").End(xlUp).Row
    For I = 1 To LR
 
        Range("Y" & I).FormulaR1C1 = "=IF(IF(R[" & I & "]C[-15]="""",R[" & I & "]C[-6],R[" & I & "]C[-15])<MASTER!RC[-23],0,1)"< p> 
       MsgBox "=IF(IF(R[" & I & "]C[-15]="""",R[" & I & "]C[-6],R[" & I & "]C[-15])<MASTER!RC[-23],0,1)"< p>  
    Next
End With
End Sub

when i check the formula in msgbox it shows the correct syntex, but when vba copy the formula in worksheet, the value of I becomes 2,4,6,8.........

Any idea what went wrong??

 

[Dryver14]

wheres the rest of the Macro?

 

[mikerickson]

That's because you are using incrimenting the offset from relative addressing. Try

Range("Y" & I).FormulaR1C1 = "=IF(IF(RC[-15]="""",RC[-6],RC[-15])"

If that works, looping is not needed.

With WS
    .Range("Y1").Value = "Deal Completed This Month"
    .Range("Z1").Value = "Opportunities Persued This Month"
    .Range("AA1").Value = "Opportunities Persued This Year"
    .Range("AB1").Value = "Inactive"
    .Range("AC1").Value = "Close Date Category"

    .Range("Y1").Resize(.Range("B65536").End(xlUp).Row, 1).FormulaR1C1 = "=IF(RC[-15]="""",RC[-6],RC[-15])" 
       
End With

 

[G2K]

thanks a ton,

Working like a charm, Great help, Massive thanks again