VBA Named Range in Excel Formula

Question

I am using the following code to add a range:

Combined_Data.Range("A1:AZ200").Find("Marker").Offset(1,0).Select  
Range(Selection, Selection.End(xlDown)).Select  
ThisWorkbook.Names.Add Name:="Marker",RefersTo:=Selection

When I use the named range "Marker" in an excel formula (typed directly into the spreadsheet), the workbook doesn't seem to respect the values contained in the range. The only way I can get it to work is to use an R1C1 reference to the cells I want in the range.

Does anyone know how I can avoid the specific cell reference?

When I look at the Name Manager, the actual cell references of the range are correct. In this example it is: A6:A1655. The Name Manager has: ='Combined Data'!$A$6:$A$1655

When I use the R1C1 reference, the range appears the same in the Name Manager.

Please give and example of use the named range "Marker" in an excel formula that gives the wrong results (create and post a small data set and expected and actual results) — chris neilsen
– chris neilsen, Commented Dec 6, 2018 at 21:18
Combined_Data is the name of a worksheet. =SUMPRODUCT(SUMIFS(Amount,Payor_Name,D$7,Marker,$B$8,Bill_Area_Data,Report_BillArea,Post_Date,Rpt_Month,Encounter_Type, Encounter_Type_Selection)) As you can see, I have several named ranges, and I am defining them all the same way (Using the code originally posted). — Phillip Kreighbaum
– Phillip Kreighbaum, Commented Dec 6, 2018 at 21:19
The formula does not error out, it just doesn't return anything. When I add the same ranges manually using the Name Manager or with a R1C1 reference in VBA, the formula returns values. — Phillip Kreighbaum
– Phillip Kreighbaum, Commented Dec 6, 2018 at 21:23

TinMan · Accepted Answer · 2018-12-06 21:18:55Z

1

All the ranges should be fully qualified to the same worksheet Combined_Data. You also need to set the Range.Find() After parameter to the first cell in the Range.

Dim marker As Range

With Combined_Data
    Set marker = .Range("A1:AZ200").Find("Marker", After:=.Range("A1"))
    If Not marker Is Nothing Then
        Set marker = marker.Offset(1, 0)
        Set marker = .Range(marker, marker.End(xlDown))
        marker.Name = "Marker"
    End If
End With

These videos will help: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset) and Excel VBA Introduction Part 15a - Find and FindNext

answered Dec 6, 2018 at 21:18

TinMan

7,8142 gold badges13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Phillip Kreighbaum Over a year ago

What does it mean for a range to be "fully qualified"?

TinMan Over a year ago

Range() is unqualified because it refers to cells on the default Worksheet. Fully Qualified refers to a range that is implicitly set to a Worksheet, (e.g. Worksheets("Sheet1").Range()). When dealing with multiple Workbooks the Worksheet should be Fully Qualified to a Workbook, Workbooks("Book1").Worksheets("Sheet1").Range()). This will ensure that you are always working with the intended Range.

TinMan Over a year ago

Cells and Ranges inside a range should also be qualified to the same Worksheet. The best way to do that is to use a With Statement. In some case it is advantages to set a Worksheet variable. Set ws = Workbooks("Book1").Worksheets("Sheet1"), Range(ws.Range(),ws.Range()), ws.Range(ws.Range(),ws.Range()), ws.Range(ws.Range(),ws.Cells()),

Phillip Kreighbaum Over a year ago

The suggested code worked, but why didn't my code work given that the ranges were listed in the Name Manager with the correct name and cell references?

TinMan Over a year ago

@PhillipKreighbaum The worksheet formula should update with the named range. I don't know why it wouldn't, if you are 100% sure that the named range is correct. I had thought that your named range may have been off due to not setting the first cell used by Range.Find().

Collectives™ on Stack Overflow

VBA Named Range in Excel Formula

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related