Long Complex Nested IFs Formula Falls to Pieces in VBA

[curtins4u]

Hi,
I have a long, complex formula in Excel which works fine. However when I recorded a macro in VBA it came out all wrong.

The formula is
=IF(RIGHT(A2,3)="000",IF(B2="R",NETWORKDAYS(C2,H2,hols),NETWORKDAYS(C2,TODAY(),hols)),IF(B2="r",(IF(F2<>"-",NETWORKDAYS(F2,H2,hols),IF(E2<>"-",NETWORKDAYS(E2,H2,hols),IF(D2<>"-",NETWORKDAYS(D2,H2,hols),"")))),NETWORKDAYS(D2,TODAY(),hols)))

In VBA this comes out as
ActiveCell.FormulaR1C1 = _
"=RIGHT(RC[-9],3)=""000"" IF(RC[-8]=""R"",NETWORKDAYS(RC[-7],RC[-2],hols),NETWORKDAYS(RC[-7],TODAY(),hols))?RC[-8]=""r"" RC[-4]<>""-"" NETWORKDAYS(RC[-4],RC[-2],hols)?RC[-5]<>""-"" NETWORKDAYS(RC[-5],RC[-2],hols)?RC[-6]<>""-"" NETWORKDAYS RC[-6] "

Any ideas how I can manually hack VBA to do what Excel does?

Jude

 

[Damon Ostrander]

Hi Jude,

So do you mean that you want to convert the formula to VBA code so that it will be evaluated by VBA? Or do you just want to use VBA to set the formula so that Excel can evaluate it as a worksheet formula?

Damon

 

[curtins4u]

Hi Damon,

I wanted to 'use VBA to set the formula so that Excel can evaluate it as a worksheet formula'. Having said that, if evaluating it in VBA is better for complex formulas that get applied to a large chunk of data (6000+ rows) then I'd like to know how to do that too.

What I'd tried to do was get VBA to paste the formula into cell J1, fill down to the end of the records before copying the results and pasting back into the same cells as values. There's a large chunk of data that it needs to be applied to and it was running slower than a lame sloth. There is a lot more processing to be done after this step and I need to re-calculate on a regular basis. Hence I was keen not to have any of the formulas that had done their job left hanging round looking for a processor to pick on.

Jude

 

[Damon Ostrander]

Hi again curtins4u,

Regarding VBA vs worksheet formulas, worksheet formulas will generally calculate faster, but VBA will give a more compact, often clearer solution. It could also be done via a VBA user-defined function (UDF). For example, the formula could be reduced to a UDF call that would look like this:

=NDAYS(A2,B2,C2,D2,E2,F2,H2)

where NDAYS contains all the logic of your formula.

But for a mere 6000 cells there is probably no need to use VBA to minimize file size or memory usage, so suggest sticking with the formula you have.

I believe the following should do what you want:

'put formula in J2
[J2].Formula = "=IF(RIGHT(A2,3)=""000"",IF(B2=""R"",NETWORKDAYS(C2,H2,hols),NETWORKDAYS(C2,TODAY(),hols)),IF(B2=""r"",(IF(F2<>""-"",NETWORKDAYS(F2,H2,hols),IF(E2<>""-"",NETWORKDAYS(E2,H2,hols),IF(D2<>""-"",NETWORKDAYS(D2,H2,hols),"""")))),NETWORKDAYS(D2,TODAY(),hols)))"

'find out how far data goes in column A and fill column J (10) down that many rows
Dim LastRow As Long
LastRow = [A65536].End(xlUp).Row
Range("J2",Cells(LastRow,10)).FillDown

I hope this helps.

Damon

 

[curtins4u]

Thanks Damon. This leads me on to ask what's the difference between
ActiveCell.FormulaR1C1 = "blah" and the form you used
[J2].Formula = "blah"?

Jude

 

[Damon Ostrander]

Hi again Jude,

I used [J2] rather than ActiveCell because it is more efficient.

[J2].Formula = "blah"

is equivalent to

[J2].Activate
ActiveCell.Formula = "blah"

It is best to avoid activating cells unnecessarily because this forces the screen to update, which usually takes considerably longer than the operation being performed on the cell.

Regarding Formula vs FormulaR1C1, there is really no difference in this context, so I just chose the shorter one. Where the difference occurs is when you want to print or view the address of a cell. For example, if I have the formula

=(A4+B4)

in cell C4, if I view the value of [c4].formula in VBA I will find that it is

=(A4+B4)

If I view the value of [c4].formulaR1C1 in VBA I will find that it is

=(RC[-2]+RC[-1])

(i.e., it will be given in row-column form RELATIVE TO cell C4)

But when setting a formula in VBA you may assign it in either A1-style or R1C1-style references to either the Formula or the FormulaR1C1 property and you will get the same result.

Damon

 

[curtins4u]

Many thanks Damon, that worked brilliantly.

Jude ray: