CODEWITHSUNDEEP
excelvba
Phillip Kreighbaum
Phillip Kreighbaum

Reputation: 21

VBA Named Range in Excel Formula

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.

Upvotes: 1

Views: 1112

Answers (1)

TinMan
TinMan

Reputation: 7759

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

Upvotes: 1

Related Questions