Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I'm sure there is a way to do this but this is a bad Excel ( as opposed to a

bad hair ) day.

I have an address in a cell pointing to another sheet.

Ex... In A1 of Sheet1 "=Sheet2!A1"

If I am on sheet 1, is there a way I can "jump" to the address pointed at...

ie; A1 Sheet2??

Copy the formula (without the = ) and paste it into a goto dialog. If doing

it manually don't forget to press escape after the ctrl-c!

I really did think there was a menu/shortcut way to do this. However, for

those who want to....

Sub subGoToAddress()

Dim slAddy As String

Dim slSheet As String

Dim slCell As String

slAddy = Mid(Selection.Formula, 2)

slSheet = Mid(slAddy, 1, InStr(slAddy, "!") - 1)

slCell = Mid(slAddy, InStr(slAddy, "!") + 1)

Sheets(slSheet).Activate

Range(slCell).Select

'

****************************************************************************

End Sub

... And pasted on a button seems to work fairly well.

There is no checking done though to see if the formula really is an address.

I thought it was interesting coding to check for an address.... There may be

a function for it... Dunno really... Here's my effort..

Sub subGoToAddress()

Dim slAddy As String

Dim slAddyR1C1 As String

Dim slSheet As String

Dim slCell As String

Dim ilBang As Integer

Dim ilErrNum As Integer

Dim slTest As String

Dim ilN As Integer

' Pick up addresses.

slAddy = Selection.Formula ' R1C1

slAddyR1C1 = Selection.FormulaR1C1

' Is it a formula?

If Len(slAddy) > 1 Then

If Left(slAddy, 1) <> "=" Then

Exit Sub

End If

' Strip the "="

slAddy = Mid(slAddy, 2)

slAddyR1C1 = Mid(slAddyR1C1, 2)

Else

Exit Sub

End If

ilBang = InStr(slAddy, "!")

If ilBang = 0 Then

' No Sheet seperator found.

' Try and assume this one.

slSheet = ActiveSheet.Name

slCell = slAddy

Else

slSheet = Mid(slAddy, 1, ilBang - 1)

slCell = Mid(slAddy, ilBang + 1)

End If

If Len(slSheet) = 0 Then

Exit Sub

End If

If Len(slAddy) = 0 Then

Exit Sub

End If

' Try and see if it's a valid address.

slTest = Replace(slAddyR1C1, "+", "")

slTest = Replace(slTest, "-", "")

slTest = Replace(slTest, "]C[", "")

slTest = Replace(slTest, "RC[", "")

slTest = Replace(slTest, "R[", "")

slTest = Replace(slTest, "]C", "")

slTest = Replace(slTest, "]", "")

For ilN = 1 To 6500

slTest = Replace(slTest, CStr(ilN), "")

Next ilN

If Len(slTest) <> 0 Then

Exit Sub

End If

Sheets(slSheet).Activate

Range(slCell).Select

End Sub

Any other offers???

Of course there's always the old error trapping way...

Sub subGoToAddress()

Dim slAddy As String

Dim slAddyR1C1 As String

Dim slSheet As String

Dim slCell As String

Dim ilBang As Integer

Dim ilErrNum As Integer

Dim slTest As String

Dim ilN As Integer

slAddy = Selection.Formula ' R1C1

slAddyR1C1 = Selection.FormulaR1C1

' Is it a formula?

If Len(slAddy) > 1 Then

If Left(slAddy, 1) <> "=" Then

Exit Sub

End If

' Strip the "="

slAddy = Mid(slAddy, 2)

slAddyR1C1 = Mid(slAddyR1C1, 2)

Else

Exit Sub

End If

ilBang = InStr(slAddy, "!")

If ilBang = 0 Then

' No Sheet seperator found.

' Try and assume this one.

slSheet = ActiveSheet.Name

slCell = slAddy

Else

slSheet = Mid(slAddy, 1, ilBang - 1)

slCell = Mid(slAddy, ilBang + 1)

End If

' Try and see if it's a valid address.

On Error Resume Next

Sheets(slSheet).Activate

ilErrNum = Err.Number

On Error GoTo 0

If ilErrNum <> 0 Then

Exit Sub

End If

Range(slCell).Select

ilErrNum = Err.Number

On Error GoTo 0

'

****************************************************************************

End Sub

u can do thru key board shortcut as "ctrl+square bracket start

buttons(next to P in my DeLL keyboard)"

Didn't find what you were looking for?
Find more on 2003 - Moving to a formula address
Or get search suggestion and latest updates.

Related Topics:

- Constructing Address formulas
- Get the PC Physical Address or Mac Address
- clearing cells value without deleting the formula I've made in cell from excel formula , HOW?
- formula help but not vba please
- Loop in formula
- Copy down formula with vba
- Array Formula
- Ctrl+Shift+Arrow Fails To Select Cells When Used in Formula Bar
- cells containing formula that refer to user-defined VBA function
- sum formula
- Manually creating a row outline with no formula anywhere
- Stuck on copying formula from 1 sheet to another
- Named formula/Windows dialog boxes
- Blank Formula bar
- Disabling Formulas, Not calculation
- Quotations in Formulas and strings
- Copy Value produced by formula in cell C3 to cell D3
- Can Any one help me writing logic for the given formula
- VBA Formula prob
- Getting a formula to copy from inside VBA
- Populate Formula(Function) in rows
- searching within formulas
- Formula using range name in VBA code
- Three Leveled Formula
- R1C1 Formula