G
Guest
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]
(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