it looks like you have a function called PrecDate that is accepting PrecTask
and Link
as as inputs.
can you share what this PrecDate function looks like?
Paul
________________________________
From: Philip <phi...@eya-bantu.co.za>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Fri, June 4, 2010 9:03:06 AM
Subject: $$Excel-Macros$$ Vba function in which a value is looked up in
referenced range from a formula result
Consider the Function I created below (to generate task dates of a
project schedule) and its procedure equivalent after that. The Test
procedure works perfectly, because the lookup function for PrecDate is
able to return the date value. But the lookup function for PrecDate
in the STARTDATE function cannot; it always returns a blank. It will
only work if the looked up value is a fixed value in the sheet, not
the result of a formula. Why this difference?
Function STARTDATE(SubNo, DetailNo, NextStart, PrecTask, WorkDays,
ProjStart As Integer, _
Link As String, LagLead As Variant, ProjectRange, HolidayRange,
ScheduleRange As Range) As Integer
With WorksheetFunction
If SubNo = 0 And DetailNo = 0 Then
STARTDATE = .VLookup(LagLead, ProjectRange, 4, 0)
ElseIf SubNo = 1 And DetailNo = 1 Then
STARTDATE = .WorkDay(ProjStart, LagLead, HolidayRange)
ElseIf DetailNo = 0 Then
STARTDATE = NextStart
Else
'PrecDate can pick up fixed values not formula values, in contracts to
Test, which can
PrecDate PrecTask, Link
Duration = IIf(Link = "SS" Or Link = "FF", 0, 1) +
IIf(Right(Link, 1) = "F", 1 - _
LagLead - WorkDays, LagLead)
STARTDATE = .WorkDay(PrecDate, Duration, HolidayRange)
End If
End With
End Function
____________________________________________________________________________________
Sub Test()
'Shortcut key: Ctrl_R
With ActiveCell
SubNo = .Offset(0, -7)
DetailNo = .Offset(0, -6)
NextStart = .Offset(1, 0)
PrecTask = .Offset(0, -5)
WorkDays = .Offset(0, -1)
ProjStart = .Offset(-2, 0)
Link = .Offset(0, 6)
LagLead = .Offset(0, -4)
End With
With WorksheetFunction
If SubNo = 0 And DetailNo = 0 Then
STARTDATE1 = .VLookup(LagLead, Range("Project"), 4, 0)
ElseIf SubNo = 1 And DetailNo = 1 Then
STARTDATE1 = .WorkDay(ProjStart, LagLead, Range("Holidays"))
ElseIf DetailNo = 0 Then
STARTDATE1 = NextStart
Else
PrecDate = .VLookup(PrecTask, Range("Schedule"),
IIf(Left(Link, 1) = "S", 10, 11), 0)
Duration = IIf(Link = "SS" Or Link = "FF", 0, 1) +
IIf(Right(Link, 1) = "F", 1 - _
LagLead - WorkDays, LagLead)
STARTDATE1 = .WorkDay(PrecDate, Duration, Range("Holidays"))
End If
End With
MsgBox Format(STARTDATE1, "dd/mm/yy")
End Sub
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
