Microsoft KB Archive/126367

= Using the Range Method to Convert String to a Range =

Article ID: 126367

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q126367



SUMMARY
In Microsoft Excel, when you use the GoTo method in a Visual Basic for Applications procedure, you must use a Visual Basic object style reference, such as the following: Worksheets("Sheet1").Range("A1") However, many functions that return worksheet or chart values return the ! style reference.

Using string manipulation, it is possible to take a ! style reference result and use it as an argument of the Range method. The Range method then converts a ! style reference to a Visual Basic object style reference that can be used with the GoTo method. The following are examples of using the Range method to accomplish this.



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

