asked on
Using variable in IF statement with NetworkDays.INTL
Need help in using a variable in the following IF statement:
Dim LastHldy As Date
Dim MaxLT As Long
Dim Holidays As String
LastHldy = Worksheets("Holidays").Cel
Holidays = "Holidays!$B2:$B" & LastHldyRow
MaxLT = [Max(ItemMaster!H:H)]
'The result of the Holidays assignment is Holidays!$B2:$B12
IF WorksheetFunction.[Network
the macro executes but I get the error "object doesn't support this property or method.
When I try the following:
IF WorksheetFunction.NetworkD
The macro does NOT execute and I get the error "Argument not optional" and .NetworkDays is highlighted.
The code works if I take out the Holidays variable.
Dim LastHldy As Date
Dim MaxLT As Long
Dim Holidays As String
LastHldy = Worksheets("Holidays").Cel
Holidays = "Holidays!$B2:$B" & LastHldyRow
MaxLT = [Max(ItemMaster!H:H)]
'The result of the Holidays assignment is Holidays!$B2:$B12
IF WorksheetFunction.[Network
the macro executes but I get the error "object doesn't support this property or method.
When I try the following:
IF WorksheetFunction.NetworkD
The macro does NOT execute and I get the error "Argument not optional" and .NetworkDays is highlighted.
The code works if I take out the Holidays variable.
Visual Basic ClassicMicrosoft ExcelVBA
Last Comment
Professor J
holiday is giving error because you cannot set range the way you did it. Try like this
With sheets("holidays)
Lastrow=.cells(.cells.rows
End with
set holidays =Range("B2:B"&Lastrow)
Or alternatively create a named range and use that in your code
Inside worksheet function refer to the holiday part like this worksheets("Holidays").Ran
With sheets("holidays)
Lastrow=.cells(.cells.rows
End with
set holidays =Range("B2:B"&Lastrow)
Or alternatively create a named range and use that in your code
Inside worksheet function refer to the holiday part like this worksheets("Holidays").Ran
ASKER
Dear ProfJJ,
Thanks but that did not work. I tried
Dim LastHldy As Date
Dim MaxLT As Long
Dim LastHldyRow As Long
Dim Holidays As Range
LastHldyRow = LastHolidayRow
With Sheets("Holidays")
LastHldy = Cells(LastHldyRow, 1).Value
Set Holidays = Range("B2:B" & LastHldyRow)
MsgBox [Holidays]
End With
LastHolidayRow is a function for determining the last row. The value of LastHldyRow is created successfully.
When I run the code above, I get a Type Mismatch error at the MsgBox line.
If I take out the MsgBox line, I get an "Application-defined or object-defined" error at the next line
If WorksheetFunction.NetworkD
Thanks but that did not work. I tried
Dim LastHldy As Date
Dim MaxLT As Long
Dim LastHldyRow As Long
Dim Holidays As Range
LastHldyRow = LastHolidayRow
With Sheets("Holidays")
LastHldy = Cells(LastHldyRow, 1).Value
Set Holidays = Range("B2:B" & LastHldyRow)
MsgBox [Holidays]
End With
LastHolidayRow is a function for determining the last row. The value of LastHldyRow is created successfully.
When I run the code above, I get a Type Mismatch error at the MsgBox line.
If I take out the MsgBox line, I get an "Application-defined or object-defined" error at the next line
If WorksheetFunction.NetworkD
ASKER
ProfJJ, on rereading your note, I see that I did not create a named range (which I would prefer not to do) so the line
If WorksheetFunction.NetworkD
will not work but I don't know why the Holidays range variable is not getting set as indicated by the MsgBox Type Mismatch error. there is also no value displayed for Holidays when I hover over it in the VB editor.
If WorksheetFunction.NetworkD
will not work but I don't know why the Holidays range variable is not getting set as indicated by the MsgBox Type Mismatch error. there is also no value displayed for Holidays when I hover over it in the VB editor.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks ProfJimJam.
The INTL_ did it for me. The following worked:
I assigned the value for LastHldyRow elsewhere.
With Sheets("Holidays")
LastHldy = Cells(LastHldyRow, 1).Value
Set HldyRange = Range("B2:B" & LastHldyRow)
End With
MaxLT = [Max(ItemMaster!H:H)]
If WorksheetFunction.NetworkD
The INTL_ did it for me. The following worked:
I assigned the value for LastHldyRow elsewhere.
With Sheets("Holidays")
LastHldy = Cells(LastHldyRow, 1).Value
Set HldyRange = Range("B2:B" & LastHldyRow)
End With
MaxLT = [Max(ItemMaster!H:H)]
If WorksheetFunction.NetworkD
EdLB
You are welcome. Thx for feedback
You are welcome. Thx for feedback
This works: If WorksheetFunction.NetworkD