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.

Dim LastHldyRow As Long, LastHldyCol As Long

Dim HldyRnge As Variant

Worksheets("Holidays").Act

Worksheets("Holidays").Use

LastHldyRow = Range("A1").SpecialCells(x

LastHldyCol = Range("A1").SpecialCells(x

Set HldyRnge = Range(Cells(1, 2), Cells(LastHldyRow, 2))

Worksheets("OpenPOs").Acti

Worksheets("OpenPOs").Used

Range("Q1") = "Remaining Time"

Range(Cells(2, 17), Cells(LastRow, 17)).FormulaR1C1 = "=NetworkDays(RC[-1],DATE(

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(

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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.

=NETWORKDAYS(P2,DATE(YEAR(

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...

Did you try the formula which i posted??

Saurabh...

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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.

"=NETWORKDAYS(RC[-1],DATE(

"=NETWORKDAYS(RC[-1],DATE(