Name Range in Excel

[Groves22]

I'm sure this is an easy fix, but I keep getting an error when I run the following code:

    With Sheets("Paste Area")
        .Cells(1, width + 3).Value = .Cells(2, width + 3).Value - .Cells(2, 21).Value
        [b].Cells(1, width + 3).Name.Add Name:="dist_to_unit_year"[/b]

How to fix this?

Thanks!

 

[SkipVought]

hi,

    With Sheets("Paste Area")
        .Cells(1, width + 3).Value = .Cells(2, width + 3).Value - .Cells(2, 21).Value
        With .Cells(1, width + 3)
            ThisWorkbook.Names.Add _
                Name:="dist_to_unit_year", _
                RefersTo:="=" & .Parent.Name & "!" & .Address

        End With
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Groves22]

Thanks Skip!

 

[Groves22]

Hey Skip...

I don't get the error, but it doesn't name the range. Why would that be?

Thanks

 

[SkipVought]

If you check out the VBA HELP example for the Names Object Add Method, you will see...

    ActiveWorkbook.Names.Add _
        Name:="tempRange", _
        RefersTo:="=Sheet1!$A$1:$D$3"

The Object reference of the Names Add Method, is the workbook object. The RANGE referece is applied to the RefersTo Property, and is in standard Excel Worksheet range format as a string, ie =SheetName!RangeReference

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Groves22]

Got it! Thanks!