Using VBA to set Name.RefersTo RRS feed

  • Question

  • I have used about 3 different methods to reset an Excel range's ReferTo property.  I have used a message box to show the string I am trying to reference (e.g. "='Sheet1'!O5:022') but instead of setting the RefersTo property to that, it sets it to another range of the same width and length (e.g., "='Sheet1!AC20:AC37).

    Does anyone have any idea why this keeps happening?


    Friday, March 2, 2018 7:46 PM

All replies

  • Does it help if you use "='Sheet1'!$O$5:$0$22'"

    Regards, Hans Vogelaar (

    Saturday, March 3, 2018 3:06 PM

  • They look similar but O and 0 are not the same.

    • Edited by James Cone Friday, March 9, 2018 1:15 PM spelling
    Thursday, March 8, 2018 2:38 AM
  • Ah! <thumbup>

    Regards, Hans Vogelaar (

    Thursday, March 8, 2018 9:27 AM
  • I don't use that technique.  If things change on the sheet then it may require 100's of changes.    I use the following technique.  If a sheet name changes I have one change.  If a column is added/deleted it requires 1 change.

    Public Enum c
      chk = 1
    End Enum
    Public Const SHEET1 As String = "Sheet1"
    Sub SetRangeExample()
      Dim ws As Worksheet
      Dim rng As Range
      Dim i As Integer
      i = 2
      Set ws = Worksheets(SHEET1)
      Set rng = ws.Range(ws.Cells(i, c.chk), ws.Cells(i, c.URL))
    End Sub

    Thursday, March 8, 2018 12:44 PM
  • I assume the number 0 in your address is a typo in this post.

    The reason your RefersTo address appears to change is because you defined it as Relative (to the activecell when the name was defined). So include the Absolute $

    If you define a Relative name with A1 as the activecell, then select B2 and return the address all will become clear!

    Saturday, March 10, 2018 10:58 AM