Link to home
Start Free TrialLog in
Avatar of EdLB
EdLB

asked on

Using range variable in Networkdays formula

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
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Excel amusant
Excel amusant

Replace your formula in your code with the following.

"=NETWORKDAYS(RC[-1],DATE(YEAR(RC[-4]),MONTH(RC[-4]),DAY(RC[-4])),HldyRnge)"


"=NETWORKDAYS(RC[-1],DATE(YEAR(RC[-4]),MONTH(RC[-4]),DAY(RC[-4])),Holidays!R[-8]C[-6])"
Avatar of EdLB

ASKER

Thanks EA but the first line of code just puts the following formula in Excel:
=NETWORKDAYS(P2,DATE(YEAR(M2),MONTH(M2),DAY(M2)),HldyRnge)

and I get a #NAME error

The second line would put variable ranges in. I want to use a fixed range from the Holidays worksheet. The range in the Holidays work sheet might change from time to time but everytime that formula is pasted in the OpenPOs worksheet, the Holidays range is the same.
EdLB,

Did you try the formula which i posted??

Saurabh...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of EdLB

ASKER

Thanks Saurabh and EA. I'm sure EA's solution would work but I can use Saurabh's without having to create a named range. Not sure why the R1C1 version of the formula creates the tic marks in the range but happy to use the non-R1C1 version.