troubleshooting Question

Using range variable in Networkdays formula

Avatar of EdLB
EdLB asked on
Visual Basic ClassicMicrosoft ExcelVB Script
6 Comments2 Solutions277 ViewsLast Modified:
Help please. The following code always throws a "Type Mismatch" error on the final line whether I dimension HldyRange as a Variant or a Range.  
Dim LastHldyRow As Long, LastHldyCol As Long
Dim HldyRnge As Variant

      Worksheets("Holidays").Activate
      Worksheets("Holidays").UsedRange
      LastHldyRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row
      LastHldyCol = Range("A1").SpecialCells(xlCellTypeLastCell).Column
      Set HldyRnge = Range(Cells(1, 2), Cells(LastHldyRow, 2))
     
      Worksheets("OpenPOs").Activate
      Worksheets("OpenPOs").UsedRange
     
      Range("Q1") = "Remaining Time"
      Range(Cells(2, 17), Cells(LastRow, 17)).FormulaR1C1 = "=NetworkDays(RC[-1],DATE(YEAR(RC[-4]),MONTH(RC[-4]),DAY(RC[-4]))," & HldyRnge & ")"

I originally tried to use the variable LastHldyRow in the formula on the last line as:
      Range(Cells(2, 17), Cells(LastRow, 17)).FormulaR1C1 = "=NetworkDays(RC[-1],DATE(YEAR(RC[-4]),MONTH(RC[-4]),DAY(RC[-4])), Holidays!B1:B" & LastHldyRow & ")"

This creates a formula in the spreadsheet but it produces the range reference in the spreadsheet formula as Holidays!B:'B11'  which produces a #NAME? error in Excel. If I modify the formula manually in excel, take the tic marks out, and use the reference range Holidays!B1:B11, it works just as expected. I don't know how to avoid the code adding tic marks.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros