VBA code to subtract on week days.

[Zimmerman]

Can anybody help me out on subtracting week days with a VBA code. I'm trying to create a sheet that will tell the user when an item need something done with it. So they would type in a dtae on cell P2 and in cell P9 I'd like to have it subtract 7 week days from cell P2. But this will all be done by clicking a command button.

Windows XP
Excel 2003

 

[Boller]

Are you able to create a worksheet formula to do what you want?

If so, then you can have the macro enter either the formula or the value returned from the formula.

 

[Zimmerman]

No i actually haven't thought of that. I guess i can try to figure this out in a formlua. Do you have an idea of how to start that. I can probably look that up if i have to.

Thanks

 

[Zimmerman]

OK i have the formula as follows.
=WORKDAY(P2,-6)
How could i get this into a VBA code?

 

[Boller]

OK i have the formula as follows.
=WORKDAY(P2,-6)
How could i get this into a VBA code?

Select a cell containing the formula.
Turn on the macro recorder.
Enter the formula in the cell.
Turn off the macro recorder.
Have a look at the code created.

 

[Zimmerman]

Sorry for being so blind to it all but that worked great thanks. One last thing that is no big deal but is there a way to not have the macro show up once it's run. meaning that if i click the cell where i recorded the macro and then run it, it shows the formula and i'd rather it didn't

 

[Boller]

If you mean that you want the cell containing the formula to show the result of the formula only then :-

Selection=Selection.Value

This does the same as Copy/PasteSpecia/?Value

 

[Zimmerman]

Yes that works perfect. Thanks so much