Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

2003 - Moving to a formula address

  Asked By: Hayfa    Date: Aug 10    Category: MS Office    Views: 970
  

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??

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Horia Ahmed     Answered On: Aug 10

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!

 
Answer #2    Answered By: Sophie Williamson     Answered On: Aug 10

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.

 
Answer #3    Answered By: Hattie Howard     Answered On: Aug 10

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???

 
Answer #4    Answered By: Adanalie Garcia     Answered On: Aug 10

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

 
Answer #5    Answered By: Ginger Snyder     Answered On: Aug 10

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.




Tagged: