VBA named range retrieves as sheetname and cell address

Question

I have a routine that adds new customer ranges to an estimate and actual sheet along with validation, formulas and protection. I add named ranges to certain cells as they are used throughout the sheets in formulas.

The code part that names the ranges is as follows (it works, the ranges are named correctly as seen in name manager)

The function that does the name ranging is declared as so

Function InitializeAddedRange(rangeToFormat As Range, _
    custName As String, currentSheet As Worksheet)

and is called as follows

 Call InitializeAddedRange(startCell, customerName, sourceWorksheet)

so in the snippet below rangeToFormat is startCell

Set rangeToFormat = rangeToFormat.Offset(0, 1)
If currentSheet.Name = "Actual Quarterly Sales" Then
    rangeToFormat.Name = Replace(custName, " ", "") & "ActualBasePrice"
    rangeToFormat.Offset(0, 1).Name = Replace(custName, " ", "") & "ActualIsCurrent"
ElseIf currentSheet.Name = "Quarterly Sales Projections" Then
    rangeToFormat.Name = Replace(custName, " ", "") & "BasePrice"
    rangeToFormat.Offset(0, 1).Name = Replace(custName, " ", "") & "IsCurrent"
End If

In a function that is called in the same subroutine but AFTER the code above, I am trying to retrieve the name of the range as its required in numerous formulas. (startCell.Name)

Set tempRange = Range(startCell.Offset(1, 3), startCell.Offset(1, 14))
For Each cCell In tempRange
    With cCell
        .Value = 0
        .NumberFormat = "$#,##0"
        .FormulaR1C1 = "=DrumGallons*" & startCell.Name & "*R[-1]C"
        .Locked = True
    End With
Next cCell

The problem is startCell.Name returns the sheetname and cell address i.e. SalesEstimates$B$12 instead of the named range which should be customernameBasePrice. I've done a lot of VBA work and I can't recall ever coming across this problem. Does this occur because the subroutine hasn't finished and the named range actually hasn't changed within excel yet before the function is trying to access it? I don't think that can be it but I've tried numerous things none of which work.

rangeToFormat is startCell, its passed as parameter to the function...I'll edit the post..As an aside, I noted that the range naming was working correctly in the original post, it was the name retrieving that was not. — dinotom, May 24 '16 at 20:54
Apparently, there's a [difference](https://msdn.microsoft.com/en-us/library/office/ff823060.aspx) between workbook and worksheet names. Edit: also [this](http://stackoverflow.com/questions/3630496/how-do-you-get-a-range-to-return-its-name)? — findwindow, May 24 '16 at 21:16
@ findwindow post that as the answer and Ill accept...it is Name.Name to retrieve the name and just Range.Name =.... to set it — dinotom, May 24 '16 at 23:34

score 0 · Answer 1 · answered May 24 '16 at 21:10

0

Can't test it so I'm just trying to recall it. try using

Name(startCell)

To obtain the name attached to the Range reference

answered May 24 '16 at 21:10

user3598756

28,893
4
18
28

@dinotom: did you get through it? – user3598756 Jun 01 '16 at 20:47

score 0 · Answer 2 · answered May 25 '16 at 15:36

0

I think you need to address the name object

rng.Name.Name

answered May 25 '16 at 15:36

Raystafarian

2,902
2
29
42

@dinotom comments don't last forever, and I didn't read them. Whatever, I'll make this a wiki, I'm not trying to steal someone's rep or anything. – Raystafarian May 30 '16 at 12:07

VBA named range retrieves as sheetname and cell address

2 Answers2