CODEWITHSUNDEEP
excelnamed-rangesaddressingvba
PBG
PBG

Reputation: 9

What Data Type is an Excel Cell Reference (VBA)?

What data type is an Excel cell reference? I have constructed strings representing cell references (e.g. "G12", "AW12", etc.), and want to use them to create named ranges;

Dim UpperLeftHandCornerOfRangeCellReference As String
UpperLeftHandCornerOfRangeCellReference = "G12"
Dim LowerRightHandCornerOfRangeCellReference As String    
LowerRightHandCornerOfRangeCellReference = "AW12"
NamedSheet.Names.Add _
            name:=RangeName, _
            RefersTo:="=Sheet!UpperLeftHandCornerOfRangeCellReference:LowerRightHandCornerOfRangeCellReference"

but, they are not interpreted as cell references, rather the names of the respective strings, and the named range thus not created. Any ideas? Please share. Thanks!

Update:

Thank you @MattCremeens for your help.

Consider if it had to be done this way;

Dim myCell1 As String
myCell1 = "G12"
Dim myCell2 As String
myCell2 = "AW12"
NamedSheet.Names.Add _
            name:=RangeName, _
            RefersTo:="=Sheet!myCell1:myCell2"

such would not work because the quotations in the 'RefersTo' parameter do not allow the string variables 'myCell(1/2)' to be read. Is there some way to fix this?

Upvotes: 0

Views: 9253

Answers (1)

Matt Cremeens
Matt Cremeens

Reputation: 5151

What I believe you are meaning to do is

Dim UpperLeftHandCornerOfRangeCellReference As Range    
Set UpperLeftHandCornerOfRangeCellReference = Range("G12")

Must use the Set keyword when assigning ranges to variables.

If you want to store your string like

myCell = "G12"

then just change the 2nd line above to

Set UpperLeftHandCornerOfRangeCellReference = Range(myCell)

Now if you had a cell that was a named range (maybe that is what you mean by a cell reference being a string??), you could do this instead

Set UpperLeftHandCornerOfRangeCellReference = Worksheets("Sheet1").Names("MyRangeName").RefersToRange

Upvotes: 2

Related Questions