If we have dates in A1 and A2, we can use the NETWORKDAYS
function in Excel to get a net workday count.
=NETWORKDAYS(A1,A2)

Is there a way to get this result in a macro?

TIA.

Set a reference to the Analysis toolpak, APTVBAEN.XLA (lower-case on my
machine) and then just call it, like so

?networkdays(date-100,date-10)
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ken" wrote in message
...
If we have dates in A1 and A2, we can use the NETWORKDAYS
function in Excel to get a net workday count.
=NETWORKDAYS(A1,A2)

Is there a way to get this result in a macro?

TIA.

Sorry, Bob, but you lost me here.
How/where do we set a reference to the Analysis toolpak?
What goes into the macro code?
Thanks for your patience.

-----Original Message-----
Set a reference to the Analysis toolpak, APTVBAEN.XLA
(lower-case on my
machine) and then just call it, like so

?networkdays(date-100,date-10)
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ken" wrote in
message
...
If we have dates in A1 and A2, we can use the
NETWORKDAYS
function in Excel to get a net workday count.
=NETWORKDAYS(A1,A2)

Is there a way to get this result in a macro?

TIA.


.

Ken,

In Excel, go to the Tools menu, choose Add-Ins, and the put a
check next to "Analysis Tool Pak - VBA". Then, go the VBA
editor, choose the Tools menu, then References. There, put a
check next to "ATPVBAEN.XLA".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ken" wrote in message
...
Sorry, Bob, but you lost me here.
How/where do we set a reference to the Analysis toolpak?
What goes into the macro code?
Thanks for your patience.

-----Original Message-----
Set a reference to the Analysis toolpak, APTVBAEN.XLA
(lower-case on my
machine) and then just call it, like so

?networkdays(date-100,date-10)
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ken" wrote in
message
...
If we have dates in A1 and A2, we can use the
NETWORKDAYS
function in Excel to get a net workday count.
=NETWORKDAYS(A1,A2)

Is there a way to get this result in a macro?

TIA.


.

So, with dates in A1 and A2 would the code to put the
networkdays in A3 be:

Sub Test()
Dim count As Integer
wks.Cells(3, 1).Value = networkdays(A2, A1)
End Sub

I am coding it wrong because it doesn't seem to work?

-----Original Message-----
Ken,

In Excel, go to the Tools menu, choose Add-Ins, and the
put a
check next to "Analysis Tool Pak - VBA". Then, go the VBA
editor, choose the Tools menu, then References. There,
put a
check next to "ATPVBAEN.XLA".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ken" wrote in
message
...
Sorry, Bob, but you lost me here.
How/where do we set a reference to the Analysis toolpak?
What goes into the macro code?
Thanks for your patience.

-----Original Message-----
Set a reference to the Analysis toolpak, APTVBAEN.XLA
(lower-case on my
machine) and then just call it, like so

?networkdays(date-100,date-10)
--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Ken" wrote in
message
...
If we have dates in A1 and A2, we can use the
NETWORKDAYS
function in Excel to get a net workday count.
=NETWORKDAYS(A1,A2)

Is there a way to get this result in a macro?

TIA.


.



.

Ken,

Use

wks.Cells(3, 1).Value = networkdays(Range("A2"),Range("A1"))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ken" wrote in message
...
So, with dates in A1 and A2 would the code to put the
networkdays in A3 be:

Sub Test()
Dim count As Integer
wks.Cells(3, 1).Value = networkdays(A2, A1)
End Sub

I am coding it wrong because it doesn't seem to work?

-----Original Message-----
Ken,

In Excel, go to the Tools menu, choose Add-Ins, and the
put a
check next to "Analysis Tool Pak - VBA". Then, go the VBA
editor, choose the Tools menu, then References. There,
put a
check next to "ATPVBAEN.XLA".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ken" wrote in
message
...
Sorry, Bob, but you lost me here.
How/where do we set a reference to the Analysis toolpak?
What goes into the macro code?
Thanks for your patience.

-----Original Message-----
Set a reference to the Analysis toolpak, APTVBAEN.XLA
(lower-case on my
machine) and then just call it, like so

?networkdays(date-100,date-10)
--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Ken" wrote in
message
...
If we have dates in A1 and A2, we can use the
NETWORKDAYS
function in Excel to get a net workday count.
=NETWORKDAYS(A1,A2)

Is there a way to get this result in a macro?

TIA.


.



.

or you could use

Application.Evaluate("NETWORKDAYS(A2,A1)")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Chip Pearson" wrote in message
...
Ken,

Use

wks.Cells(3, 1).Value = networkdays(Range("A2"),Range("A1"))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ken" wrote in message
...
So, with dates in A1 and A2 would the code to put the
networkdays in A3 be:

Sub Test()
Dim count As Integer
wks.Cells(3, 1).Value = networkdays(A2, A1)
End Sub

I am coding it wrong because it doesn't seem to work?

-----Original Message-----
Ken,

In Excel, go to the Tools menu, choose Add-Ins, and the
put a
check next to "Analysis Tool Pak - VBA". Then, go the VBA
editor, choose the Tools menu, then References. There,
put a
check next to "ATPVBAEN.XLA".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ken" wrote in
message
...
Sorry, Bob, but you lost me here.
How/where do we set a reference to the Analysis toolpak?
What goes into the macro code?
Thanks for your patience.

-----Original Message-----
Set a reference to the Analysis toolpak, APTVBAEN.XLA
(lower-case on my
machine) and then just call it, like so

?networkdays(date-100,date-10)
--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Ken" wrote in
message
...
If we have dates in A1 and A2, we can use the
NETWORKDAYS
function in Excel to get a net workday count.
=NETWORKDAYS(A1,A2)

Is there a way to get this result in a macro?

TIA.


.



.

When I ran it, I got a:

"Run time error '424':
Object Required

error message?

-----Original Message-----
Ken,

Use

wks.Cells(3, 1).Value = networkdays(Range("A2"),Range
("A1"))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ken" wrote in
message
...
So, with dates in A1 and A2 would the code to put the
networkdays in A3 be:

Sub Test()
Dim count As Integer
wks.Cells(3, 1).Value = networkdays(A2, A1)
End Sub

I am coding it wrong because it doesn't seem to work?

-----Original Message-----
Ken,

In Excel, go to the Tools menu, choose Add-Ins, and the
put a
check next to "Analysis Tool Pak - VBA". Then, go the
VBA
editor, choose the Tools menu, then References. There,
put a
check next to "ATPVBAEN.XLA".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ken" wrote in
message
...
Sorry, Bob, but you lost me here.
How/where do we set a reference to the Analysis
toolpak?
What goes into the macro code?
Thanks for your patience.

-----Original Message-----
Set a reference to the Analysis toolpak,
APTVBAEN.XLA
(lower-case on my
machine) and then just call it, like so

?networkdays(date-100,date-10)
--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Ken" wrote in
message
...
If we have dates in A1 and A2, we can use the
NETWORKDAYS
function in Excel to get a net workday count.
=NETWORKDAYS(A1,A2)

Is there a way to get this result in a macro?

TIA.


.



.



.

Did you set the reference as described?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ken" wrote in message
...
When I ran it, I got a:

"Run time error '424':
Object Required

error message?

-----Original Message-----
Ken,

Use

wks.Cells(3, 1).Value = networkdays(Range("A2"),Range
("A1"))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ken" wrote in
message
...
So, with dates in A1 and A2 would the code to put the
networkdays in A3 be:

Sub Test()
Dim count As Integer
wks.Cells(3, 1).Value = networkdays(A2, A1)
End Sub

I am coding it wrong because it doesn't seem to work?

-----Original Message-----
Ken,

In Excel, go to the Tools menu, choose Add-Ins, and the
put a
check next to "Analysis Tool Pak - VBA". Then, go the
VBA
editor, choose the Tools menu, then References. There,
put a
check next to "ATPVBAEN.XLA".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ken" wrote in
message
...
Sorry, Bob, but you lost me here.
How/where do we set a reference to the Analysis
toolpak?
What goes into the macro code?
Thanks for your patience.

-----Original Message-----
Set a reference to the Analysis toolpak,
APTVBAEN.XLA
(lower-case on my
machine) and then just call it, like so

?networkdays(date-100,date-10)
--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Ken" wrote in
message
...
If we have dates in A1 and A2, we can use the
NETWORKDAYS
function in Excel to get a net workday count.
=NETWORKDAYS(A1,A2)

Is there a way to get this result in a macro?

TIA.


.



.



.

Yes, although my reference is atpvbaen.xls not
atpvbaen.xla.

Anyway, I entered the following code and got it to work.

Sub Test()
Dim count As Integer
count = Application.Evaluate("NETWORKDAYS(A2,A1)")
Range("A3").Select
ActiveCell.Value = count
End Sub

-----Original Message-----
Did you set the reference as described?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ken" wrote in
message
...
When I ran it, I got a:

"Run time error '424':
Object Required

error message?

-----Original Message-----
Ken,

Use

wks.Cells(3, 1).Value = networkdays(Range
("A2"),Range
("A1"))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ken" wrote in
message
...
So, with dates in A1 and A2 would the code to put the
networkdays in A3 be:

Sub Test()
Dim count As Integer
wks.Cells(3, 1).Value = networkdays(A2, A1)
End Sub

I am coding it wrong because it doesn't seem to work?

-----Original Message-----
Ken,

In Excel, go to the Tools menu, choose Add-Ins, and
the
put a
check next to "Analysis Tool Pak - VBA". Then, go
the
VBA
editor, choose the Tools menu, then References.
There,
put a
check next to "ATPVBAEN.XLA".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ken" wrote in
message
...
Sorry, Bob, but you lost me here.
How/where do we set a reference to the Analysis
toolpak?
What goes into the macro code?
Thanks for your patience.

-----Original Message-----
Set a reference to the Analysis toolpak,
APTVBAEN.XLA
(lower-case on my
machine) and then just call it, like so

?networkdays(date-100,date-10)
--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Ken"
wrote in
message
...
If we have dates in A1 and A2, we can use the
NETWORKDAYS
function in Excel to get a net workday count.
=NETWORKDAYS(A1,A2)

Is there a way to get this result in a macro?

TIA.


.



.



.



.

You are right, so is mine. Mine works though<G

Sorry no other ideas at the moment. Have you tried my Evaluate suggestion,
it shouldn't make any difference but you never know.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ken" wrote in message
...
Yes, although my reference is atpvbaen.xls not
atpvbaen.xla.

Anyway, I entered the following code and got it to work.

Sub Test()
Dim count As Integer
count = Application.Evaluate("NETWORKDAYS(A2,A1)")
Range("A3").Select
ActiveCell.Value = count
End Sub

-----Original Message-----
Did you set the reference as described?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ken" wrote in
message
...
When I ran it, I got a:

"Run time error '424':
Object Required

error message?

-----Original Message-----
Ken,

Use

wks.Cells(3, 1).Value = networkdays(Range
("A2"),Range
("A1"))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ken" wrote in
message
...
So, with dates in A1 and A2 would the code to put the
networkdays in A3 be:

Sub Test()
Dim count As Integer
wks.Cells(3, 1).Value = networkdays(A2, A1)
End Sub

I am coding it wrong because it doesn't seem to work?

-----Original Message-----
Ken,

In Excel, go to the Tools menu, choose Add-Ins, and
the
put a
check next to "Analysis Tool Pak - VBA". Then, go
the
VBA
editor, choose the Tools menu, then References.
There,
put a
check next to "ATPVBAEN.XLA".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ken" wrote in
message
...
Sorry, Bob, but you lost me here.
How/where do we set a reference to the Analysis
toolpak?
What goes into the macro code?
Thanks for your patience.

-----Original Message-----
Set a reference to the Analysis toolpak,
APTVBAEN.XLA
(lower-case on my
machine) and then just call it, like so

?networkdays(date-100,date-10)
--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Ken"
wrote in
message
...
If we have dates in A1 and A2, we can use the
NETWORKDAYS
function in Excel to get a net workday count.
=NETWORKDAYS(A1,A2)

Is there a way to get this result in a macro?

TIA.


.



.



.



.