goto function in macro 2

[mohavv]

Hi,

I probably didn't express myself enough, so I give it another try.

Casus:
sheet 1 cell A1 contains following: =sheet3!D19

I want to use GOTO (F5) with the contents of A1 to go to that cell

I thought this would do it, but unfortunately not.

Sub Macro1()

Dim tmp As String

tmp = Range("a1").Formula

Application.Goto Reference:=tmp

End Sub

When I add this the A4 text becomes: text =sheet3!D19

Range("A4").Formula = "text " & tmp

What am I doing wrong?

Cheers,

Harold

 

[ShaneDevenshire]

Hi,

Try:

Sub GoToIt()
It = Mid([A1].Formula, 2, 99)
Range(It).Activate
End Sub

Your problem is that the first character in the formula is "=". The Mid
function above starts at the 2nd character instead.

 

[mohavv]

Hi,

Try:

Sub GoToIt()
    It = Mid([A1].Formula, 2, 99)
    Range(It).Activate
End Sub

Your problem is that the first character in the formula is "=".  The Mid
function above starts at the 2nd character instead.

--
Cheers,
Shane Devenshire

Hi,

I probably didn't express myself enough, so I give it another try.

Casus:
sheet 1 cell A1 contains following: =sheet3!D19

I want to use GOTO (F5) with the contents of A1 to go to that cell

I thought this would do it, but unfortunately not.

Sub Macro1()

Dim tmp As String

tmp = Range("a1").Formula

Application.Goto Reference:=tmp

End Sub

When I add this the A4 text becomes: text =sheet3!D19

    Range("A4").Formula = "text " & tmp

What am I doing wrong?

Harold

Unfortunately not the answer.
Same problem

I work with Excel 2007, FYI

As soon as you type the short code like "it" it doesn't work.
If you type the text itself it work fine.

Cheers,

Harold

 

[Bob Phillips]

Try this

Sub Macro1()

Dim tmp As String

tmp = Range("a1").Formula

Application.Goto Reference:=Range(tmp)

End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[ShaneDevenshire]

Hi,

Try this:

Sub GoToIt()
Dim myAddress As String, myEnd As Integer, mySheet As String, myCell As
String
myAddress = Mid([A1].Formula, 2, 100)
myEnd = InStr(1, myAddress, "!")
mySheet = Mid([A1].Formula, 2, myEnd - 1)
myCell = Mid(myAddress, myEnd + 1, 100)
Application.Goto Worksheets(mySheet).Range(myCell)
End Sub


--


Cheers,
Shane Devenshire

Hi,

Try:

Sub GoToIt()
It = Mid([A1].Formula, 2, 99)
Range(It).Activate
End Sub

Your problem is that the first character in the formula is "=". The Mid
function above starts at the 2nd character instead.

--
Cheers,
Shane Devenshire

Hi,

I probably didn't express myself enough, so I give it another try.

Casus:
sheet 1 cell A1 contains following: =sheet3!D19

I want to use GOTO (F5) with the contents of A1 to go to that cell

I thought this would do it, but unfortunately not.

Sub Macro1()

Dim tmp As String

tmp = Range("a1").Formula

Application.Goto Reference:=tmp

End Sub

When I add this the A4 text becomes: text =sheet3!D19

Range("A4").Formula = "text " & tmp

What am I doing wrong?

Harold

Unfortunately not the answer.
Same problem

I work with Excel 2007, FYI

As soon as you type the short code like "it" it doesn't work.
If you type the text itself it work fine.

Cheers,

Harold

 

[mohavv]

Hi,

Try this:

Sub GoToIt()
    Dim myAddress As String, myEnd As Integer, mySheet As String, myCell As
String
    myAddress = Mid([A1].Formula, 2, 100)
    myEnd = InStr(1, myAddress, "!")
    mySheet = Mid([A1].Formula, 2, myEnd - 1)
    myCell = Mid(myAddress, myEnd + 1, 100)
    Application.Goto Worksheets(mySheet).Range(myCell)
End Sub

--

Cheers,
Shane Devenshire

Hi,
Try:
Sub GoToIt()
    It = Mid([A1].Formula, 2, 99)
    Range(It).Activate
End Sub
Your problem is that the first character in the formula is "=".  The Mid
function above starts at the 2nd character instead.
--
Cheers,
Shane Devenshire
:
Hi,
I probably didn't express myself enough, so I give it another try.
Casus:
sheet 1 cell A1 contains following: =sheet3!D19
I want to use GOTO (F5) with the contents of A1 to go to that cell
I thought this would do it, but unfortunately not.
Sub Macro1()
Dim tmp As String
tmp = Range("a1").Formula
Application.Goto Reference:=tmp
End Sub
When I add this the A4 text becomes: text =sheet3!D19
    Range("A4").Formula = "text " & tmp
What am I doing wrong?
Cheers,
Harold

Unfortunately not the answer.
Same problem

I work with Excel 2007, FYI

As soon as you type the short code like "it" it doesn't work.
If you type the text itself it work fine.

Harold- Hide quoted text -

- Show quoted text -

this works, but this will not work when the link is to an other
workbook.

If I do it manually it works fine. This is what I normally do.

I hit F2, mark the complete link, hit CTRL C, hit ESC, hit F5, hit
CTRL V and hit enter.
It doesn't matter if it starts with "=" or "=+".

If anyone has another idea....

Cheers,

Harold