Using the Range Method to Convert String to a Range

   Worksheets("Sheet1").Range("A1")
				

MORE INFORMATION

Converting Link Formula

To convert a simple Microsoft Excel link formula, for example "=Sheet1!$A$1" to a Visual Basic style reference, you can strip the equal sign (=) from the left side of the string and pass the remainder of the formula to the Range object.

The following line of code selects the cell or range that is referenced by the formula in the active cell:

   Application.GoTo _
      Range(Right(ActiveCell.Formula, Len(ActiveCell.Formula - 1))
				

Converting Reference in Chart Series

To select a range that is referenced as a y value in the formula of a chart series, the string manipulation becomes a bit more complex. The following procedure strips the section of the series formula between the second and third commas of the currently selected series and passes that string to the Range object to select it.

Sub Select_The_Y_Values()

  Dim StartString As Integer
  Dim EndString As Integer
  Dim SeriesForm As String

    ' Assign the formula property of the selected series to
    ' the variable "SeriesForm"
    SeriesForm = Selection.Formula

    ' If a point is selected rather than the entire series, you will
    ' need to modify the above line to the following
    '  Selection.Parent.Formula

    ' Find the position of the first character after the second
    ' comma in the formula
    StartString = InStr(InStr(SeriesForm, ",") + 1, SeriesForm, ",") + 1

    ' Find the position of the third comma
    EndString = InStr(StartString, SeriesForm, ",")

    ' Strip out the y values (between the second and third commas)
    ref = Mid(SeriesForm, StartString, EndString - StartString)

    ' Pass the Y values to the Range object and select it
    Application.GoTo Range(ref)

End Sub