Apply IF condition to a range Excel VBA

Question

I have written this code but it doesnt seems to be working , Why?

Dim oRo As String
Dim nRo As String


Lastro = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
oRo = "J2:O" & Lastro
nRo = "Q2:V" & Lastro


Range("C2:G" & Lastro).Select

With Range("C2:G" & Lastro)
.Range("C2:G" & Lastro).Formula = "=IF(oRo*nRo<0,ROUNDUP(oRo*1.3,-2),IF(ABS(oRo)    <=ABS(nRo),nRo,ROUNDUP(oRo*1.3,-2)))"
End With    

End Sub

Answer 1

Your best bet for maintainability is to embrace R1C1 notation when you write formulas to Excel from VBA. I still can't read R1C1, but I use it exclusively to write formulas from VBA. Here's how:

Let's say you want this formula in G2

=IF(J2*Q2<0,ROUNDUP(J2*1.3,-2),IF(ABS(J2)<=ABS(Q2),Q2,ROUNDUP(J2*1.3,-2)))

So type that in G2, select G2, and open the Immediate Window (Ctrl+G in VBE). In the IW, type

?activecell.FormulaR1C1

That will give you all that you need. You don't have to be able to read it, you just have to be sure you typed the right formula (in A1 notation) in cell G2. Now you can have super simple code like

Dim lRow As Long
Dim sh As Worksheet

Set sh = ActiveSheet

lRow = sh.Cells(sh.Rows.Count, 2).End(xlUp).Row - 1
sh.Range("G2").Resize(lRow, 1).FormulaR1C1 = "=IF(RC[3]*RC[10]<0,ROUNDUP(RC[3]*1.3,-2),IF(ABS(RC[3])<=ABS(RC[10]),RC[10],ROUNDUP(RC[3]*1.3,-2)))"

All I did was copy the R1C1 formula from the Immediate Window and paste it into the code.

Answer 2

.Range("C2:G" & Lastro).Formula = "=IF(" & oRo & "*" & nRo & "<0,ROUNDUP(" & oRo & "*1.3,-2),IF(ABS(" & oRo & ")    <=ABS(" & nRo & ")," & nRo & ",ROUNDUP(" & oRo & "*1.3,-2)))"

You are hardcoding the phrases "oRo" and "nRo"

Answer 3

When you get to this line to write the formula, you are not writing out the values of oRo and nRo, so excel is expectin them to be defined within name manager.

.Range("C2:G" & Lastro).Formula = "=IF(oRo*nRo<0,ROUNDUP(oRo*1.3,-2),IF(ABS(oRo)    <=ABS(nRo),nRo,ROUNDUP(oRo*1.3,-2)))"

If you want to write out the calculated values of oRo and nRo, you will have to alter the code slightly:

Dim oRo As String
Dim nRo As String

Lastro = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
oRo = "J2:O" & Lastro
nRo = "Q2:V" & Lastro

Range("C2:G" & Lastro).Formula = _
    "=IF(" & oRo & "*" & nRo & "<0,ROUNDUP(" & oRo & _
    "*1.3,-2),IF(ABS(" & oRo & ")    <=ABS(" & nRo & _
    ")," & nRo & ",ROUNDUP(" & oRo & "*1.3,-2)))"

End Sub

notice no select is required, and as you are only dealing with one range, no with is required either. I would also suggest you add in Option Explicit to the top of your procedure to ensure all variables are declared and spelled correctly.