Excel 2010 - Reference Workbook Scope Named Range in Sheet Code

[AleksArcher]

Hi all -

I am getting a Run-time error '1004' Method 'Range' of object '_Worksheet' failed on the following line:

hSheet.Cells(arow, 7) = cStart + Range(Domestic_FSR_Series7_Duration_d).Value

This project is bit of stretch for me using named range values in worksheet code. The range "Domestic_FSR_Series7_Duration_d" is storing 42 as a value.

Any guidance is very much appreciated

Here is the whole code (thus far) for reference:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim hSheet As Worksheet
Dim cSite As String
Dim cProgram As String
Dim cLocation As String
Dim cReq As Long
Dim cStart As Long

Set hSheet = Sheets("Hiring Schedule")
arow = Target.Row
aCol = Target.Column

If Not IsNumeric(hSheet.Cells(arow, 2)) And hSheet.Cells(arow, 2) <> "" _
And Not IsNumeric(hSheet.Cells(arow, 3)) And hSheet.Cells(arow, 3) <> "" _
And Not IsNumeric(hSheet.Cells(arow, 4)) And hSheet.Cells(arow, 4) <> "" _
And IsNumeric(hSheet.Cells(arow, 5)) And hSheet.Cells(arow, 5) <> "" _
And Not IsNumeric(hSheet.Cells(arow, 6)) And hSheet.Cells(arow, 6) <> "" Then

    cSite = hSheet.Cells(arow, 2)
    cProgram = hSheet.Cells(arow, 3)
    cLocation = hSheet.Cells(arow, 4)
    cReq = hSheet.Cells(arow, 5)
    cStart = CDate(hSheet.Cells(arow, 6).Value)
    hSheet.Cells(arow, 7) = cStart + Range(Domestic_FSR_Series7_Duration_d).Value
    
    
End If

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
You need to wrap your named range in quotes

hSheet.Cells(arow, 7) = cStart + Range("Domestic_FSR_Series7_Duration_d").Value

But it will only work if the named range is a single cell.

 

[AleksArcher]

Hi Fluff. Thanks for replying! I gave it a shot wrapping the reference in " to no avail

hSheet.Cells(arow, 7) = cStart + Range("Domestic_FSR_Series7_Duration_d").Value

The named range isn't actually referencing a cell, it's just holding the value 42 (from the Name Manager)

 

[Fluff]

In that case I'm not sure.
I've never come across using a "named range" that isn't a range.

 

[AleksArcher]

Alright, so I tried changing the Named Range to referencing a cell and still no solution. If I reference the range outside of the Sheet Code for Hiring Schedule in a Module, the range works fine

Sheets("Hiring Schedule").Cells(5, 5) = Range("Domestic_FSR_Series7_Duration_d").Value

but if I run the same code in the sheet code, it fails. Is there some method to reference a Workbook scope Named Range in sheet code?

Sub Worksheet_Change(ByVal Target As Range)

Dim hSheet As Worksheet
Dim cSite As String
Dim cProgram As String
Dim cLocation As String
Dim cReq As Long
Dim cStart As Long

Set hSheet = Sheets("Hiring Schedule")
arow = Target.Row
aCol = Target.Column

If Not IsNumeric(hSheet.Cells(arow, 2)) And hSheet.Cells(arow, 2) <> "" _
And Not IsNumeric(hSheet.Cells(arow, 3)) And hSheet.Cells(arow, 3) <> "" _
And Not IsNumeric(hSheet.Cells(arow, 4)) And hSheet.Cells(arow, 4) <> "" _
And IsNumeric(hSheet.Cells(arow, 5)) And hSheet.Cells(arow, 5) <> "" _
And Not IsNumeric(hSheet.Cells(arow, 6)) And hSheet.Cells(arow, 6) <> "" Then

    cSite = hSheet.Cells(arow, 2)
    cProgram = hSheet.Cells(arow, 3)
    cLocation = hSheet.Cells(arow, 4)
    cReq = hSheet.Cells(arow, 5)
    cStart = CDate(hSheet.Cells(arow, 6).Value)
    hSheet.Cells(arow, 7) = cStart + Range("Domestic_FSR_Series7_Duration_d").Value
        
End If

End Sub

 

[AleksArcher]

I figured it out. The range needed to be referred to at the Workbook level. So appending "wb.Application." fixed the error:

Working code below:

Sub Worksheet_Change(ByVal Target As Range)

Dim hSheet As Worksheet
Dim cSite As String
Dim cProgram As String
Dim cLocation As String
Dim cReq As Long
Dim cStart As Long
Dim wb As Workbook

Set wb = Workbooks("Finance Headcount Model (New)")
Set hSheet = Sheets("Hiring Schedule")
arow = Target.Row
aCol = Target.Column

If Not IsNumeric(hSheet.Cells(arow, 2)) And hSheet.Cells(arow, 2) <> "" _
And Not IsNumeric(hSheet.Cells(arow, 3)) And hSheet.Cells(arow, 3) <> "" _
And Not IsNumeric(hSheet.Cells(arow, 4)) And hSheet.Cells(arow, 4) <> "" _
And IsNumeric(hSheet.Cells(arow, 5)) And hSheet.Cells(arow, 5) <> "" _
And Not IsNumeric(hSheet.Cells(arow, 6)) And hSheet.Cells(arow, 6) <> "" Then

    cSite = hSheet.Cells(arow, 2)
    cProgram = hSheet.Cells(arow, 3)
    cLocation = hSheet.Cells(arow, 4)
    cReq = hSheet.Cells(arow, 5)
    cStart = CDate(hSheet.Cells(arow, 6).Value)
    hSheet.Cells(arow, 7) = Format(cStart + wb.Application.Range("Domestic_FSR_Series7_Duration_d"), "yyyy-mm-dd")
        
End If

End Sub