goto function in macro 2
 

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

goto function in macro 2
 

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


"mohavv" wrote:

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

goto function in macro 2
 

On Jan 21, 7:48*pm, ShaneDevenshire
wrote:
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

"mohavv" wrote:
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.

Cheers,

Harold

goto function in macro 2
 

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)



"mohavv" wrote in message
...
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

goto function in macro 2
 

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


"mohavv" wrote:

On Jan 21, 7:48 pm, ShaneDevenshire
wrote:
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

"mohavv" wrote:
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.

Cheers,

Harold

goto function in macro 2
 

On Jan 22, 4:29*am, ShaneDevenshire
wrote:
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



"mohavv" wrote:
On Jan 21, 7:48 pm, ShaneDevenshire
wrote:
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

"mohavv" wrote:
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.

Cheers,

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