Calculating with workdays and extra days

[Flash0220]

Hello,


I'm using Excel 2003, and i'm having the following problem with calculating days.

A user will type a date in cel A1.
Now some calculating has to be done, and the result has to be placed in cell B1.

Let's say the value in cell A1 is november 1st 2011.

I now need VBA code which adds 10 days to that date.
The VBA code may not count weekends (saturdays and sundays) and may also not
add/calculate any "special" dates stored in a list on another tab/sheet with the name "Holidays" (range A10:A50).
For this example there are 2 dates (November 3rd and November 9th) which are in
that 10 day range and are in that list.

To the date to be calculated has to be november 17th 2011.

Is there a way to do this in Excel VBA 2003 ?

Thanks for helping.


Flash0220

 

[VoG]

Without VBA

=WORKDAY(A1,10,Holidays!A10:A50)

This requires the Analysis Toolpak from Tools > Add-ins.

 

[Flash0220]

Hello,


Thanks for your fast reply.

The problem is that my employer does not want a formula version, but
only a VBA version.

The formula version works fine, but unfortunately i need a VBA version.


Best regards,
Flash0220

 

[VoG]

Try like this

Sub test()
Range("B1").Value = Evaluate("WORKDAY(A1,10,Holidays!A10:A50)")
End Sub

 

[Flash0220]

Hello,


Thanks again for the fast reply.

I inserted your code in my program, modified it a little, but i now get an error.

' Collumn A is selected.

' Selecting the cell 3 positions to the right and store the value (a date) in "DateLetterSend".
ActiveCell.Offset(0, 3).Select
DateLetterSend = ActiveCell.Value

' Moving 1 cell to the right to get to the cell where the result has to be generated.
ActiveCell.Offset(0, 1).Select

' The VBA code which should generate the result.
ActiveCell.Value = Evaluate("WORKDAY(DateLetterSend,10,Holidays!A10:A50)")

When i run the code i get a "#NAME?" ("#NAAM?" in dutch Excel 2003 version) error.
I loaded the Analysis Toolpak add-in but i still get this error.

I can't figure out what's wrong with my code.

Can anyone help me out ?
Thanks


Best regards,
Flash0220

(A not very experienced VBA user....)

 

[VoG]

Try WERKDAG instead of WORKDAY.

 

[Jonmo1]

Try

ActiveCell.Value = Evaluate("WORKDAY(" & DateLetterSend & ",10,Holidays!A10:A50)")

 

[Flash0220]

Hello,


ActiveCell.Value = Evaluate("WERKDAG(DateLetterSend,10,Holidays!A10:A50)")
also generates the "#NAAM?" error.

The VBA editor recognises the "Evaluate" command as a VBA command, so that command
can not cause the error, as far as i know.

ActiveCell.Value = Evaluate("WERKDAG(" & DateLetterSend & ",10,Holidays!A10:A50)")
generates the "#NAAM" error.

ActiveCell.Value = Evaluate("WORKDAY(" & DateLetterSend & ",10,Holidays!A10:A50)")
generates a "#N/B" error.

Weird....


Best regards,
Flash0220

 

[VoG]

Sorry, I don't know why it isn't working. It worked when I tested it.

Can you get the worksheet formula to work?

 

[rcbunting]

Try adding quotes to the string like this:

Sub test()

    Dim DateLetterSend As Date
    DateLetterSend = ActiveCell.Offset(0, 3).Value
    
    ActiveCell.Offset(0, 4).Value = (Evaluate("WORKDAY(""" & DateLetterSend & """,10,Sheet2!A10:A50)"))

End Sub