Long Complex Nested IFs Formula Falls to Pieces in VBA

curtins4u

New Member
Joined
Jan 21, 2005
Messages
21
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 :confused:
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

New Member
Joined
Jan 21, 2005
Messages
21
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 :rolleyes:
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

New Member
Joined
Jan 21, 2005
Messages
21

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Forum statistics

Threads
1,148,215
Messages
5,745,424
Members
423,951
Latest member
peggrif

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top