I have a sheet that has 5 columns
(DayName,CalendarDay,Impact,Holiday,Result). The basic idea is that it has
every day in the year listed (and actually goes out to the beginning of
2008). The Impact column is what holds the modifier for dates. If there is a
holiday then we have to add a Plus/Minus modifier in this column which tells
the formula to add or subtract days for cutoffs on delivery or receiving. Now
what I am trying to do (from a module not formula because formulas get to
messy in large spreadsheets) is write a function that does a lookup on this
page and returns the Impact for that day of the year. However, when I run my
code I get NOTHING, ZIP, not even a ZERO back. I put error code in to see if
the code was getting an error and nothing.

Also if anyone has a better example for the data section I am ready to hear
it. At this point I figure to cut the page data down by removing days that
have 0 Impact. I have inherited this spreadsheet and I am trying to update it
to work better. We have several people who use versions of this spreadsheet
and make mistakes on it because they do not understand the formulas, so I am
putting all that I can into modules.

[sample data]
Day Impact Holiday Result

Sat 12/24/05 -1 Christmas Eve 12/23/05
Sun 12/25/05 -2 Christmas Day 12/23/05
Mon 12/26/05 0 12/26/05
Tue 12/27/05 0 12/27/05
Wed 12/28/05 0 12/28/05
Thu 12/29/05 0 12/29/05
Fri 12/30/05 0 12/30/05
Sat 12/31/05 -1 New Years Eve 12/30/05
Sun 01/01/06 -2 New Years Day 12/30/05

[/sample data]

It sounds to me that you are trying to emulate the NETWORKDAYS Analysis
Toolpak function, which calculates days between two dates, and can ignore
holidays.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Stanley" wrote in message
...
I have a sheet that has 5 columns
(DayName,CalendarDay,Impact,Holiday,Result). The basic idea is that it has
every day in the year listed (and actually goes out to the beginning of
2008). The Impact column is what holds the modifier for dates. If there is
a
holiday then we have to add a Plus/Minus modifier in this column which
tells
the formula to add or subtract days for cutoffs on delivery or receiving.
Now
what I am trying to do (from a module not formula because formulas get to
messy in large spreadsheets) is write a function that does a lookup on
this
page and returns the Impact for that day of the year. However, when I run
my
code I get NOTHING, ZIP, not even a ZERO back. I put error code in to see
if
the code was getting an error and nothing.

Also if anyone has a better example for the data section I am ready to
hear
it. At this point I figure to cut the page data down by removing days that
have 0 Impact. I have inherited this spreadsheet and I am trying to update
it
to work better. We have several people who use versions of this
spreadsheet
and make mistakes on it because they do not understand the formulas, so I
am
putting all that I can into modules.

[sample data]
Day Impact Holiday Result

Sat 12/24/05 -1 Christmas Eve 12/23/05
Sun 12/25/05 -2 Christmas Day 12/23/05
Mon 12/26/05 0 12/26/05
Tue 12/27/05 0 12/27/05
Wed 12/28/05 0 12/28/05
Thu 12/29/05 0 12/29/05
Fri 12/30/05 0 12/30/05
Sat 12/31/05 -1 New Years Eve 12/30/05
Sun 01/01/06 -2 New Years Day 12/30/05

[/sample data]

Code:

 Function doLookup(data As String)
 On Error GoTo ProcessError
     If LCase(data) = "omit" Or data = "" Then
         doLookup = 0
         Exit Function
     End If
     Dim data1 As Date
     data1 = data
     Dim myRange As Range
     Set myRange = Worksheets("Tables").Range("$B$4:$C$1100")

     MsgBox (Application.WorksheetFunction.VLookup(data1, myRange, 2,

Actually no that does not help at all. If the day that a item is shipped out
falls on a holiday I need to calculate weather to set the cutoff earlier than
that day or in the event that an item in scheduled to be received on a
weekend and the monday is a holiday then I need to advance the pickup date
beyond the holiday or weekend. The weekend part is not a problem it is the
lookup of the holidays.

"Bob Phillips" wrote:

It sounds to me that you are trying to emulate the NETWORKDAYS Analysis
Toolpak function, which calculates days between two dates, and can ignore
holidays.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Stanley" wrote in message
...
I have a sheet that has 5 columns
(DayName,CalendarDay,Impact,Holiday,Result). The basic idea is that it has
every day in the year listed (and actually goes out to the beginning of
2008). The Impact column is what holds the modifier for dates. If there is
a
holiday then we have to add a Plus/Minus modifier in this column which
tells
the formula to add or subtract days for cutoffs on delivery or receiving.
Now
what I am trying to do (from a module not formula because formulas get to
messy in large spreadsheets) is write a function that does a lookup on
this
page and returns the Impact for that day of the year. However, when I run
my
code I get NOTHING, ZIP, not even a ZERO back. I put error code in to see
if
the code was getting an error and nothing.

Also if anyone has a better example for the data section I am ready to
hear
it. At this point I figure to cut the page data down by removing days that
have 0 Impact. I have inherited this spreadsheet and I am trying to update
it
to work better. We have several people who use versions of this
spreadsheet
and make mistakes on it because they do not understand the formulas, so I
am
putting all that I can into modules.

[sample data]
Day Impact Holiday Result

Sat 12/24/05 -1 Christmas Eve 12/23/05
Sun 12/25/05 -2 Christmas Day 12/23/05
Mon 12/26/05 0 12/26/05
Tue 12/27/05 0 12/27/05
Wed 12/28/05 0 12/28/05
Thu 12/29/05 0 12/29/05
Fri 12/30/05 0 12/30/05
Sat 12/31/05 -1 New Years Eve 12/30/05
Sun 01/01/06 -2 New Years Day 12/30/05

[/sample data]

Code:

  Function doLookup(data As String)
  On Error GoTo ProcessError
      If LCase(data) = "omit" Or data = "" Then
          doLookup = 0
          Exit Function
      End If
      Dim data1 As Date
      data1 = data
      Dim myRange As Range
      Set myRange = Worksheets("Tables").Range("$B$4:$C$1100")
 
      MsgBox (Application.WorksheetFunction.VLookup(data1, myRange, 2,

Code:

 False))
      Exit Function
 
  ProcessError:
      MsgBox (Err.Description)
      Resume
  End Function
  

This one does it:

Function doLookup(data)
On Error GoTo ProcessError
If LCase(data) = "omit" Or data = "" Then
doLookup = 0
Exit Function
End If
Dim data1 As Double
data1 = data
Dim myRange As Range
Set myRange = Range("$B$4:$C$1100")

MsgBox (Application.VLookup(data1, myRange, 2, False))
Exit Function

ProcessError:
MsgBox (Err.Description)
Resume
End Function

But there are still problems with your error routine, which is in an
infinite loop

--
Kind regards,

Niek Otten


"Stanley" wrote in message
...
I have a sheet that has 5 columns
(DayName,CalendarDay,Impact,Holiday,Result). The basic idea is that it has
every day in the year listed (and actually goes out to the beginning of
2008). The Impact column is what holds the modifier for dates. If there is
a
holiday then we have to add a Plus/Minus modifier in this column which
tells
the formula to add or subtract days for cutoffs on delivery or receiving.
Now
what I am trying to do (from a module not formula because formulas get to
messy in large spreadsheets) is write a function that does a lookup on
this
page and returns the Impact for that day of the year. However, when I run
my
code I get NOTHING, ZIP, not even a ZERO back. I put error code in to see
if
the code was getting an error and nothing.

Also if anyone has a better example for the data section I am ready to
hear
it. At this point I figure to cut the page data down by removing days that
have 0 Impact. I have inherited this spreadsheet and I am trying to update
it
to work better. We have several people who use versions of this
spreadsheet
and make mistakes on it because they do not understand the formulas, so I
am
putting all that I can into modules.

[sample data]
Day Impact Holiday Result

Sat 12/24/05 -1 Christmas Eve 12/23/05
Sun 12/25/05 -2 Christmas Day 12/23/05
Mon 12/26/05 0 12/26/05
Tue 12/27/05 0 12/27/05
Wed 12/28/05 0 12/28/05
Thu 12/29/05 0 12/29/05
Fri 12/30/05 0 12/30/05
Sat 12/31/05 -1 New Years Eve 12/30/05
Sun 01/01/06 -2 New Years Day 12/30/05

[/sample data]

Code:

 Function doLookup(data As String)
 On Error GoTo ProcessError
    If LCase(data) = "omit" Or data = "" Then
        doLookup = 0
        Exit Function
    End If
    Dim data1 As Date
    data1 = data
    Dim myRange As Range
    Set myRange = Worksheets("Tables").Range("$B$4:$C$1100")

    MsgBox (Application.WorksheetFunction.VLookup(data1, myRange, 2,
 False))
    Exit Function

 ProcessError:
    MsgBox (Err.Description)
    Resume
 End Function

Thanks that helped.

"Niek Otten" wrote:

This one does it:

Function doLookup(data)
On Error GoTo ProcessError
If LCase(data) = "omit" Or data = "" Then
doLookup = 0
Exit Function
End If
Dim data1 As Double
data1 = data
Dim myRange As Range
Set myRange = Range("$B$4:$C$1100")

MsgBox (Application.VLookup(data1, myRange, 2, False))
Exit Function

ProcessError:
MsgBox (Err.Description)
Resume
End Function

But there are still problems with your error routine, which is in an
infinite loop

--
Kind regards,

Niek Otten


"Stanley" wrote in message
...
I have a sheet that has 5 columns
(DayName,CalendarDay,Impact,Holiday,Result). The basic idea is that it has
every day in the year listed (and actually goes out to the beginning of
2008). The Impact column is what holds the modifier for dates. If there is
a
holiday then we have to add a Plus/Minus modifier in this column which
tells
the formula to add or subtract days for cutoffs on delivery or receiving.
Now
what I am trying to do (from a module not formula because formulas get to
messy in large spreadsheets) is write a function that does a lookup on
this
page and returns the Impact for that day of the year. However, when I run
my
code I get NOTHING, ZIP, not even a ZERO back. I put error code in to see
if
the code was getting an error and nothing.

Also if anyone has a better example for the data section I am ready to
hear
it. At this point I figure to cut the page data down by removing days that
have 0 Impact. I have inherited this spreadsheet and I am trying to update
it
to work better. We have several people who use versions of this
spreadsheet
and make mistakes on it because they do not understand the formulas, so I
am
putting all that I can into modules.

[sample data]
Day Impact Holiday Result

Sat 12/24/05 -1 Christmas Eve 12/23/05
Sun 12/25/05 -2 Christmas Day 12/23/05
Mon 12/26/05 0 12/26/05
Tue 12/27/05 0 12/27/05
Wed 12/28/05 0 12/28/05
Thu 12/29/05 0 12/29/05
Fri 12/30/05 0 12/30/05
Sat 12/31/05 -1 New Years Eve 12/30/05
Sun 01/01/06 -2 New Years Day 12/30/05

[/sample data]

Code:

  Function doLookup(data As String)
  On Error GoTo ProcessError
     If LCase(data) = "omit" Or data = "" Then
         doLookup = 0
         Exit Function
     End If
     Dim data1 As Date
     data1 = data
     Dim myRange As Range
     Set myRange = Worksheets("Tables").Range("$B$4:$C$1100")
 
     MsgBox (Application.WorksheetFunction.VLookup(data1, myRange, 2,
  False))
     Exit Function
 
  ProcessError:
     MsgBox (Err.Description)
     Resume
  End Function
 

So how about putting the holidays in a list, say M1:M20, and use Conditional
Formatting on the pickup date (say cell D2)with a formula of

=ISNUMBER(MATCH(D2,$M$1:$M$20,0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Stanley" wrote in message
...
Actually no that does not help at all. If the day that a item is shipped
out
falls on a holiday I need to calculate weather to set the cutoff earlier
than
that day or in the event that an item in scheduled to be received on a
weekend and the monday is a holiday then I need to advance the pickup date
beyond the holiday or weekend. The weekend part is not a problem it is the
lookup of the holidays.

"Bob Phillips" wrote:

It sounds to me that you are trying to emulate the NETWORKDAYS Analysis
Toolpak function, which calculates days between two dates, and can
ignore
holidays.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Stanley" wrote in message
...
I have a sheet that has 5 columns
(DayName,CalendarDay,Impact,Holiday,Result). The basic idea is that it
has
every day in the year listed (and actually goes out to the beginning
of
2008). The Impact column is what holds the modifier for dates. If
there is
a
holiday then we have to add a Plus/Minus modifier in this column which
tells
the formula to add or subtract days for cutoffs on delivery or
receiving.
Now
what I am trying to do (from a module not formula because formulas get
to
messy in large spreadsheets) is write a function that does a lookup on
this
page and returns the Impact for that day of the year. However, when I
run
my
code I get NOTHING, ZIP, not even a ZERO back. I put error code in to
see
if
the code was getting an error and nothing.

Also if anyone has a better example for the data section I am ready to
hear
it. At this point I figure to cut the page data down by removing days
that
have 0 Impact. I have inherited this spreadsheet and I am trying to
update
it
to work better. We have several people who use versions of this
spreadsheet
and make mistakes on it because they do not understand the formulas,
so I
am
putting all that I can into modules.

[sample data]
Day Impact Holiday Result

Sat 12/24/05 -1 Christmas Eve 12/23/05
Sun 12/25/05 -2 Christmas Day 12/23/05
Mon 12/26/05 0 12/26/05
Tue 12/27/05 0 12/27/05
Wed 12/28/05 0 12/28/05
Thu 12/29/05 0 12/29/05
Fri 12/30/05 0 12/30/05
Sat 12/31/05 -1 New Years Eve 12/30/05
Sun 01/01/06 -2 New Years Day 12/30/05

[/sample data]

Code:

   Function doLookup(data As String)
   On Error GoTo ProcessError
       If LCase(data) = "omit" Or data = "" Then
           doLookup = 0
           Exit Function
       End If
       Dim data1 As Date
       data1 = data
       Dim myRange As Range
       Set myRange = Worksheets("Tables").Range("$B$4:$C$1100")
  
       MsgBox (Application.WorksheetFunction.VLookup(data1, myRange, 2,

Code:

  False))
       Exit Function
  
   ProcessError:
       MsgBox (Err.Description)
       Resume
   End Function