using a formula in VBA

[Zabman]

I have a formula that works fine when placed in cells as below:

=WORKDAY(J7,IF(HOUR(K7)>10,3,2),PubHols)

but I cannot get this to translate into VBA - I have tried this, as well as many other things I have searched for on these forums but cant seem to get it right:

range("Main_FTX").Offset(irows) = worksheetfunction.workday(range("Main_ETADate").Offset(irows)),if(hour(range("Main_ETATime").Offset(irows))>10,3,2),range("PubHols"))

among other things but cannot get the syntax correct. I could put the formula in the cell, then copy value only and paste it over, but i'd prefer to do it this way.

Can anyone help?

 

[Jonmo1]

Workday is part of the Analysis Toolpack Addin.
And is not a memeber of WorksheetFunction.

You have to install the addin
Analsys Toolpack - VBA

Then, in your vba project, click Tools - References
And put a check on atpvbaen.xls

Then use it like

x = Workday(startdate, enddate)

No Application or WorksheetFunction required.


Hope that helps.

 

[Zabman]

Hi.

I definately have the Analysis packs installed - I know this as I can see the 2 projects in the project window. I have taken away the worksheetfunction prefix but am getting a compile error expected end of statememnt where I have underlined:

range("Main_FTX").Offset(irows) = workday(range("Main_ETADate").Offset(irows)),if(hour(range("Main_ETATime").Offset(irows))>10,3,2),range("PubHols"))

 

[Jonmo1]

A few things.

1. It's not just that the addin must be installed. You also need to set a reference to it.
In VBA, while your cursor is inside your current module, click Tools - References and put a check on atpvbaen.xls

2. You have an extra )
3. In Vba, use iif(expression,true,false) instead of if

Try

range("Main_FTX").Offset(irows) = workday(range("Main_ETADate").Offset(irows),iif(hour(range("Main_ETATime").Offset(irows))>10,3,2),range("PubHols"))

 

[Zabman]

Hi,

I am getting a compile error, saying Sud or Function not defined, highlighting WorkDay, when I attempted to run the line you suggested. I have confirmed atpvbaen.xls is checked under references.

Im annoyed I missed the other two things, I should have picked those up before I posted here.