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.
Microsoft ExcelVB ScriptVisual Basic Classic

Avatar of undefined
Last Comment
EdLB

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Saurabh Singh Teotia

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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])"
ASKER
EdLB

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.
Saurabh Singh Teotia

EdLB,

Did you try the formula which i posted??

Saurabh...
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Excel amusant

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
EdLB

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.