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
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.
.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"
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.
