Calculating Working hours in a Week

A stab in the dark

Code:

---

' other code
Dim myHolidays As String
' other code
If myLocation = "WHUK" Then
    myHolidays = Names("Hols_WHIL").RefersTo
ElseIf myLocation = "WHPHL" Then
    myHolidays = Names("Hols_PH").RefersTo
End If
' other code

---

why don't you just declare your ranges in the sub to avoid confusion?

Code:

---


dim Hols_WHIL As Range
set Hols_WHIL = Range("...somerangevalues...")
If myLocation = "WHUK" Then
    myHolidays = Hols_WHIL

---

Couple of things I see

1. In the test sub and in the Function definition, 'myLocation' is a string, and there is no named range called 'myLocation' so Range("myLocation') won't work

2. Hols_WHIL and Hols_WHUAE have #REF errors

Code:

---

Sub test()
    MsgBox WorkingHours3("WHUK", #6/1/2011#, 8, 8, 4, 8, 5, 0, 0)
End Sub
 
Public Function WorkingHours3(ByVal myLocation As String, ByVal myMonth As Date, _
                              ByVal myMonHrs As Double, ByVal myTueHrs As Double, ByVal myWedHrs As Double, _
                              ByVal myThursHrs As Double, ByVal myFriHrs As Double, ByVal mySatHrs As Double, _
                              ByVal mySunHrs As Double) As Double
    Dim myDateLoop As Long, myHolidayHours As Long
    Dim myHoursWorked As Double
    Dim myHolidays As Range
myHoursWorked = 0#
If Range("myLocation").Value = "WHUK" Then
        myHolidays = Range("Hols_WHUK")

---

3. Since myHolidays is Dim-ed as a range object you need to use Set

Code:

---

      Set myHolidays = Range("Hols_WHUK")

---

4. On sheet 'Working hrs calc', B4 and B5 you have UK and PHL, but that's not one of the cases in the If/Then/Elseif so it keeps on going

5. Suggest you have a final 'Else' to make a error condition

Code:

---

    If myLocation = "WHUK" Then            ' tested as string
    Set myHolidays = Range("Hols_WHUK")  ' added Set
    ElseIf myLocation = "WHIL" Then
    Set myHolidays = Range("Hols_WHIL") ' range is not defined
    ElseIf myLocation = "WHUAE" Then
    Set myHolidays = Range("Hols_WHUAE")    ' range is not defined
    ElseIf myLocation = "WHPHL" Then
    Set myHolidays = Range("Hols_WHPH")
    Else                                    ' added a error catcher
    MsgBox "bad parameter"
    WorkingHours3 = -999
    Exit Function
    End If

---

Paul

Hi Frank,
I am still getting the same error.
Updated excel file with vba code enclosed for your ready reference..

thanks for looking into..

regards

Quote:

---

Originally Posted by frank_m

A stab in the dark

Code:

---

' other code
    Dim myHolidays As String
    ' other code
If myLocation = "WHUK" Then
    myHolidays = Names("Hols_WHIL").RefersTo
ElseIf myLocation = "WHPHL" Then
    myHolidays = Names("Hols_PH").RefersTo
End If
    ' other code

---

---

Paul,
thanks a ton; will revert shortly...

Quote:

---

Originally Posted by Paul_Hossler

Couple of things I see

1. In the test sub and in the Function definition, 'myLocation' is a string, and there is no named range called 'myLocation' so Range("myLocation') won't work

2. Hols_WHIL and Hols_WHUAE have #REF errors

Code:

---

Sub test()
    MsgBox WorkingHours3("WHUK", #6/1/2011#, 8, 8, 4, 8, 5, 0, 0)
End Sub
 
Public Function WorkingHours3(ByVal myLocation As String, ByVal myMonth As Date, _
                              ByVal myMonHrs As Double, ByVal myTueHrs As Double, ByVal myWedHrs As Double, _
                              ByVal myThursHrs As Double, ByVal myFriHrs As Double, ByVal mySatHrs As Double, _
                              ByVal mySunHrs As Double) As Double
    Dim myDateLoop As Long, myHolidayHours As Long
    Dim myHoursWorked As Double
    Dim myHolidays As Range
    myHoursWorked = 0#
    If Range("myLocation").Value = "WHUK" Then
        myHolidays = Range("Hols_WHUK")

---

3. Since myHolidays is Dim-ed as a range object you need to use Set

Code:

---

      Set myHolidays = Range("Hols_WHUK")

---

4. On sheet 'Working hrs calc', B4 and B5 you have UK and PHL, but that's not one of the cases in the If/Then/Elseif so it keeps on going

5. Suggest you have a final 'Else' to make a error condition

Code:

---

    If myLocation = "WHUK" Then            ' tested as string
    Set myHolidays = Range("Hols_WHUK")  ' added Set
    ElseIf myLocation = "WHIL" Then
    Set myHolidays = Range("Hols_WHIL") ' range is not defined
    ElseIf myLocation = "WHUAE" Then
    Set myHolidays = Range("Hols_WHUAE")    ' range is not defined
    ElseIf myLocation = "WHPHL" Then
    Set myHolidays = Range("Hols_WHPH")
    Else                                    ' added a error catcher
    MsgBox "bad parameter"
    WorkingHours3 = -999
    Exit Function
    End If

---

Paul

---

Many many thanks a TON Paul. Works just fine...

Thank you everyone for responses..

surya