Application.Goto Reference:="ActiveCell.Value"??? Whats wrong??

Hello fellow programmers! I am in need of some help trying to get my
macros to goto a specific location from a sheet...I'd figure the code
would look like this...

Application.Goto Reference:="ActiveCell.Value"

The problem is I am told my refrence isn't correct..however I have
tried to format it the way it works when I use the "Goto" function of
Excel (CRTL + G) the format I am using in the active cell is...

SheetDB1!B65500

When I copy this code and use the goto function it works fine but the
macro is failing to realize what it is I am trying to do...Any ideas?

Suppress the quotes!
Application.Goto Reference:=ActiveCell.Value

HTH
--
AP

<Shaka215@gmail.com> a écrit dans le message de news:
1152693389.042004.5190@m79g2000cwm.googlegroups.com...
> Hello fellow programmers! I am in need of some help trying to get my
> macros to goto a specific location from a sheet...I'd figure the code
> would look like this...
>
> Application.Goto Reference:="ActiveCell.Value"
>
> The problem is I am told my refrence isn't correct..however I have
> tried to format it the way it works when I use the "Goto" function of
> Excel (CRTL + G) the format I am using in the active cell is...
>
> SheetDB1!B65500
>
> When I copy this code and use the goto function it works fine but the
> macro is failing to realize what it is I am trying to do...Any ideas?
>

Ardus Petus...

Still getting "Run-time error '1004' Reference is not valid." Even if
its a simple

Application.Goto ActiveCell.Value

it still craps out...any thoughts?


Ardus Petus wrote:
> Suppress the quotes!
> Application.Goto Reference:=3DActiveCell.Value
>
> HTH
> --
> AP
>
> <Shaka215@gmail.com> a =E9crit dans le message de news:
> 1152693389.042004.5190@m79g2000cwm.googlegroups.com...
> > Hello fellow programmers! I am in need of some help trying to get my
> > macros to goto a specific location from a sheet...I'd figure the code
> > would look like this...
> >
> > Application.Goto Reference:=3D"ActiveCell.Value"
> >
> > The problem is I am told my refrence isn't correct..however I have
> > tried to format it the way it works when I use the "Goto" function of
> > Excel (CRTL + G) the format I am using in the active cell is...
> >
> > SheetDB1!B65500
> >
> > When I copy this code and use the goto function it works fine but the
> > macro is failing to realize what it is I am trying to do...Any ideas?
> >

I just realized that when the format is in the DB1!R[2]C[-13] it works
however I don't understand the refrencing of that format..Row 2 Column
-13? Is there a better way of getting the macro to understand that I
want it to goto ??

SHEET NAME : DB1
Cell: A65500

Thanks your help is much appreciated.

<According to Help for .GoTo>
......Reference Optional Variant. The destination. Can be a Range object, a
string that contains a cell reference in R1C1-style notation,....
</According to Help for .GoTo>

So, you have to convert your address to the correct format and remove the
[Bookx.xls] that gets added
Application.Goto Reference:=Mid(Application.ConvertFormula(ActiveCell.Value,
xlA1, xlR1C1), Len(ActiveCell.Parent.Name) + 6)

I doubt this is the best way of achieving this, but it works.

NickHK

<Shaka215@gmail.com> wrote in message
news:1152693389.042004.5190@m79g2000cwm.googlegroups.com...
> Hello fellow programmers! I am in need of some help trying to get my
> macros to goto a specific location from a sheet...I'd figure the code
> would look like this...
>
> Application.Goto Reference:="ActiveCell.Value"
>
> The problem is I am told my refrence isn't correct..however I have
> tried to format it the way it works when I use the "Goto" function of
> Excel (CRTL + G) the format I am using in the active cell is...
>
> SheetDB1!B65500
>
> When I copy this code and use the goto function it works fine but the
> macro is failing to realize what it is I am trying to do...Any ideas?
>

Nick I like your way of thinking but that isn't working either...the
[Bookx.xls] isn't in the activecell.value code and its not ever going
to be...I simply want VBA to understand that it is to goto a specific
area of the individual workbook and do something (which Ill program it
to do) however the refrence error happens with your code...even when I
put in the workbook name inside of the activecell.value... thanks for
atleast attempting to slove the issue.


NickHK wrote:
> <According to Help for .GoTo>
> .....Reference Optional Variant. The destination. Can be a Range object, a
> string that contains a cell reference in R1C1-style notation,....
> </According to Help for .GoTo>
>
> So, you have to convert your address to the correct format and remove the
> [Bookx.xls] that gets added
> Application.Goto Reference:=Mid(Application.ConvertFormula(ActiveCell.Value,
> xlA1, xlR1C1), Len(ActiveCell.Parent.Name) + 6)
>
> I doubt this is the best way of achieving this, but it works.
>
> NickHK
>
> <Shaka215@gmail.com> wrote in message
> news:1152693389.042004.5190@m79g2000cwm.googlegroups.com...
> > Hello fellow programmers! I am in need of some help trying to get my
> > macros to goto a specific location from a sheet...I'd figure the code
> > would look like this...
> >
> > Application.Goto Reference:="ActiveCell.Value"
> >
> > The problem is I am told my refrence isn't correct..however I have
> > tried to format it the way it works when I use the "Goto" function of
> > Excel (CRTL + G) the format I am using in the active cell is...
> >
> > SheetDB1!B65500
> >
> > When I copy this code and use the goto function it works fine but the
> > macro is failing to realize what it is I am trying to do...Any ideas?
> >

You do not need to "put in the workbook name inside of the
activecell.value".
It is generated by .ConvertFormula and consequently must be removed to give
a valid value for .Destination.

NickHK

<Shaka215@gmail.com> wrote in message
news:1152695485.473464.130930@35g2000cwc.googlegroups.com...
> Nick I like your way of thinking but that isn't working either...the
> [Bookx.xls] isn't in the activecell.value code and its not ever going
> to be...I simply want VBA to understand that it is to goto a specific
> area of the individual workbook and do something (which Ill program it
> to do) however the refrence error happens with your code...even when I
> put in the workbook name inside of the activecell.value... thanks for
> atleast attempting to slove the issue.
>
>
> NickHK wrote:
> > <According to Help for .GoTo>
> > .....Reference Optional Variant. The destination. Can be a Range
object, a
> > string that contains a cell reference in R1C1-style notation,....
> > </According to Help for .GoTo>
> >
> > So, you have to convert your address to the correct format and remove
the
> > [Bookx.xls] that gets added
> > Application.Goto
Reference:=Mid(Application.ConvertFormula(ActiveCell.Value,
> > xlA1, xlR1C1), Len(ActiveCell.Parent.Name) + 6)
> >
> > I doubt this is the best way of achieving this, but it works.
> >
> > NickHK
> >
> > <Shaka215@gmail.com> wrote in message
> > news:1152693389.042004.5190@m79g2000cwm.googlegroups.com...
> > > Hello fellow programmers! I am in need of some help trying to get my
> > > macros to goto a specific location from a sheet...I'd figure the code
> > > would look like this...
> > >
> > > Application.Goto Reference:="ActiveCell.Value"
> > >
> > > The problem is I am told my refrence isn't correct..however I have
> > > tried to format it the way it works when I use the "Goto" function of
> > > Excel (CRTL + G) the format I am using in the active cell is...
> > >
> > > SheetDB1!B65500
> > >
> > > When I copy this code and use the goto function it works fine but the
> > > macro is failing to realize what it is I am trying to do...Any ideas?
> > >
>

Nick,

Check your code because it isn't working...I keep getting told that the
refrence is invalid. It seems that even with the understanding that the
workbook name is inserted once its converted it still isn't making a
diffrence. I only want my macro to understand that it is to goto...

Sheet: DB1
Cell: B65500

There for its "DB1!B65500" in the activecell.value ?????? Is there a
pieace of code that converts the DB1!B565500 into the crappy refrenced
format of "DB1!R[2]C[-13]" without having to actually change the
properties of the cell, I mean even it does I can work around this but
somehow my formatting has to be changed by the macro and since I never
used the 'crappy formatting' then I am at a lost...


NickHK wrote:
> You do not need to "put in the workbook name inside of the
> activecell.value".
> It is generated by .ConvertFormula and consequently must be removed to give
> a valid value for .Destination.
>
> NickHK
>
> <Shaka215@gmail.com> wrote in message
> news:1152695485.473464.130930@35g2000cwc.googlegroups.com...
> > Nick I like your way of thinking but that isn't working either...the
> > [Bookx.xls] isn't in the activecell.value code and its not ever going
> > to be...I simply want VBA to understand that it is to goto a specific
> > area of the individual workbook and do something (which Ill program it
> > to do) however the refrence error happens with your code...even when I
> > put in the workbook name inside of the activecell.value... thanks for
> > atleast attempting to slove the issue.
> >
> >
> > NickHK wrote:
> > > <According to Help for .GoTo>
> > > .....Reference Optional Variant. The destination. Can be a Range
> object, a
> > > string that contains a cell reference in R1C1-style notation,....
> > > </According to Help for .GoTo>
> > >
> > > So, you have to convert your address to the correct format and remove
> the
> > > [Bookx.xls] that gets added
> > > Application.Goto
> Reference:=Mid(Application.ConvertFormula(ActiveCell.Value,
> > > xlA1, xlR1C1), Len(ActiveCell.Parent.Name) + 6)
> > >
> > > I doubt this is the best way of achieving this, but it works.
> > >
> > > NickHK
> > >
> > > <Shaka215@gmail.com> wrote in message
> > > news:1152693389.042004.5190@m79g2000cwm.googlegroups.com...
> > > > Hello fellow programmers! I am in need of some help trying to get my
> > > > macros to goto a specific location from a sheet...I'd figure the code
> > > > would look like this...
> > > >
> > > > Application.Goto Reference:="ActiveCell.Value"
> > > >
> > > > The problem is I am told my refrence isn't correct..however I have
> > > > tried to format it the way it works when I use the "Goto" function of
> > > > Excel (CRTL + G) the format I am using in the active cell is...
> > > >
> > > > SheetDB1!B65500
> > > >
> > > > When I copy this code and use the goto function it works fine but the
> > > > macro is failing to realize what it is I am trying to do...Any ideas?
> > > >
> >

You could try some debugging yourself, to check the value returned at each
stage.

Correction:
Mid(Application.ConvertFormula(ActiveCell.Value, xlA1, xlR1C1),
Len(ActiveCell.Parent.Parent.Name) + 3)

NickHK

<Shaka215@gmail.com> wrote in message
news:1152696421.360250.54960@p79g2000cwp.googlegroups.com...
> Nick,
>
> Check your code because it isn't working...I keep getting told that the
> refrence is invalid. It seems that even with the understanding that the
> workbook name is inserted once its converted it still isn't making a
> diffrence. I only want my macro to understand that it is to goto...
>
> Sheet: DB1
> Cell: B65500
>
> There for its "DB1!B65500" in the activecell.value ?????? Is there a
> pieace of code that converts the DB1!B565500 into the crappy refrenced
> format of "DB1!R[2]C[-13]" without having to actually change the
> properties of the cell, I mean even it does I can work around this but
> somehow my formatting has to be changed by the macro and since I never
> used the 'crappy formatting' then I am at a lost...
>
>
> NickHK wrote:
> > You do not need to "put in the workbook name inside of the
> > activecell.value".
> > It is generated by .ConvertFormula and consequently must be removed to
give
> > a valid value for .Destination.
> >
> > NickHK
> >
> > <Shaka215@gmail.com> wrote in message
> > news:1152695485.473464.130930@35g2000cwc.googlegroups.com...
> > > Nick I like your way of thinking but that isn't working either...the
> > > [Bookx.xls] isn't in the activecell.value code and its not ever going
> > > to be...I simply want VBA to understand that it is to goto a specific
> > > area of the individual workbook and do something (which Ill program it
> > > to do) however the refrence error happens with your code...even when I
> > > put in the workbook name inside of the activecell.value... thanks for
> > > atleast attempting to slove the issue.
> > >
> > >
> > > NickHK wrote:
> > > > <According to Help for .GoTo>
> > > > .....Reference Optional Variant. The destination. Can be a Range
> > object, a
> > > > string that contains a cell reference in R1C1-style notation,....
> > > > </According to Help for .GoTo>
> > > >
> > > > So, you have to convert your address to the correct format and
remove
> > the
> > > > [Bookx.xls] that gets added
> > > > Application.Goto
> > Reference:=Mid(Application.ConvertFormula(ActiveCell.Value,
> > > > xlA1, xlR1C1), Len(ActiveCell.Parent.Name) + 6)
> > > >
> > > > I doubt this is the best way of achieving this, but it works.
> > > >
> > > > NickHK
> > > >
> > > > <Shaka215@gmail.com> wrote in message
> > > > news:1152693389.042004.5190@m79g2000cwm.googlegroups.com...
> > > > > Hello fellow programmers! I am in need of some help trying to get
my
> > > > > macros to goto a specific location from a sheet...I'd figure the
code
> > > > > would look like this...
> > > > >
> > > > > Application.Goto Reference:="ActiveCell.Value"
> > > > >
> > > > > The problem is I am told my refrence isn't correct..however I have
> > > > > tried to format it the way it works when I use the "Goto" function
of
> > > > > Excel (CRTL + G) the format I am using in the active cell is...
> > > > >
> > > > > SheetDB1!B65500
> > > > >
> > > > > When I copy this code and use the goto function it works fine but
the
> > > > > macro is failing to realize what it is I am trying to do...Any
ideas?
> > > > >
> > >
>

VBA does not need to "GoTo" or "select" or "activate" a cell to do something
to it or read something from it: that will just slow down your VBA to a
crawl.

all you need to do is to reference the cell:
worksheet("SheetDB1").Range("B65500")=123.456
or
worksheet("SheetDB1").Range("A1").offset(65499,1)=123.456
or
worksheet("SheetDB1").Cells(65500,2)=123.456
or ....

regards
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

<Shaka215@gmail.com> wrote in message
news:1152695485.473464.130930@35g2000cwc.googlegroups.com...
> Nick I like your way of thinking but that isn't working either...the
> [Bookx.xls] isn't in the activecell.value code and its not ever going
> to be...I simply want VBA to understand that it is to goto a specific
> area of the individual workbook and do something (which Ill program it
> to do) however the refrence error happens with your code...even when I
> put in the workbook name inside of the activecell.value... thanks for
> atleast attempting to slove the issue.
>
>
> NickHK wrote:
>> <According to Help for .GoTo>
>> .....Reference Optional Variant. The destination. Can be a Range
>> object, a
>> string that contains a cell reference in R1C1-style notation,....
>> </According to Help for .GoTo>
>>
>> So, you have to convert your address to the correct format and remove the
>> [Bookx.xls] that gets added
>> Application.Goto
>> Reference:=Mid(Application.ConvertFormula(ActiveCell.Value,
>> xlA1, xlR1C1), Len(ActiveCell.Parent.Name) + 6)
>>
>> I doubt this is the best way of achieving this, but it works.
>>
>> NickHK
>>
>> <Shaka215@gmail.com> wrote in message
>> news:1152693389.042004.5190@m79g2000cwm.googlegroups.com...
>> > Hello fellow programmers! I am in need of some help trying to get my
>> > macros to goto a specific location from a sheet...I'd figure the code
>> > would look like this...
>> >
>> > Application.Goto Reference:="ActiveCell.Value"
>> >
>> > The problem is I am told my refrence isn't correct..however I have
>> > tried to format it the way it works when I use the "Goto" function of
>> > Excel (CRTL + G) the format I am using in the active cell is...
>> >
>> > SheetDB1!B65500
>> >
>> > When I copy this code and use the goto function it works fine but the
>> > macro is failing to realize what it is I am trying to do...Any ideas?
>> >
>

Norma's code worked great...thank you for trying to help.


Charles Williams wrote:
> VBA does not need to "GoTo" or "select" or "activate" a cell to do something
> to it or read something from it: that will just slow down your VBA to a
> crawl.
>
> all you need to do is to reference the cell:
> worksheet("SheetDB1").Range("B65500")=123.456
> or
> worksheet("SheetDB1").Range("A1").offset(65499,1)=123.456
> or
> worksheet("SheetDB1").Cells(65500,2)=123.456
> or ....
>
> regards
> Charles
> ______________________
> Decision Models
> FastExcel 2.2 Beta now available
> www.DecisionModels.com
>
> <Shaka215@gmail.com> wrote in message
> news:1152695485.473464.130930@35g2000cwc.googlegroups.com...
> > Nick I like your way of thinking but that isn't working either...the
> > [Bookx.xls] isn't in the activecell.value code and its not ever going
> > to be...I simply want VBA to understand that it is to goto a specific
> > area of the individual workbook and do something (which Ill program it
> > to do) however the refrence error happens with your code...even when I
> > put in the workbook name inside of the activecell.value... thanks for
> > atleast attempting to slove the issue.
> >
> >
> > NickHK wrote:
> >> <According to Help for .GoTo>
> >> .....Reference Optional Variant. The destination. Can be a Range
> >> object, a
> >> string that contains a cell reference in R1C1-style notation,....
> >> </According to Help for .GoTo>
> >>
> >> So, you have to convert your address to the correct format and remove the
> >> [Bookx.xls] that gets added
> >> Application.Goto
> >> Reference:=Mid(Application.ConvertFormula(ActiveCell.Value,
> >> xlA1, xlR1C1), Len(ActiveCell.Parent.Name) + 6)
> >>
> >> I doubt this is the best way of achieving this, but it works.
> >>
> >> NickHK
> >>
> >> <Shaka215@gmail.com> wrote in message
> >> news:1152693389.042004.5190@m79g2000cwm.googlegroups.com...
> >> > Hello fellow programmers! I am in need of some help trying to get my
> >> > macros to goto a specific location from a sheet...I'd figure the code
> >> > would look like this...
> >> >
> >> > Application.Goto Reference:="ActiveCell.Value"
> >> >
> >> > The problem is I am told my refrence isn't correct..however I have
> >> > tried to format it the way it works when I use the "Goto" function of
> >> > Excel (CRTL + G) the format I am using in the active cell is...
> >> >
> >> > SheetDB1!B65500
> >> >
> >> > When I copy this code and use the goto function it works fine but the
> >> > macro is failing to realize what it is I am trying to do...Any ideas?
> >> >
> >

Hi Shaka,

Try:

'=============>>
Public Sub TesterA01()
Dim SH As Worksheet
Dim arr As Variant
Dim rng As Range

arr = Split(ActiveCell.Value, "!")

Set SH = Sheets(arr(0))
Set rng = SH.Range(arr(1))

Application.Goto Reference:=rng

End Sub
'<<=============


---
Regards,
Norman



<Shaka215@gmail.com> wrote in message
news:1152693389.042004.5190@m79g2000cwm.googlegroups.com...
> Hello fellow programmers! I am in need of some help trying to get my
> macros to goto a specific location from a sheet...I'd figure the code
> would look like this...
>
> Application.Goto Reference:="ActiveCell.Value"
>
> The problem is I am told my refrence isn't correct..however I have
> tried to format it the way it works when I use the "Goto" function of
> Excel (CRTL + G) the format I am using in the active cell is...
>
> SheetDB1!B65500
>
> When I copy this code and use the goto function it works fine but the
> macro is failing to realize what it is I am trying to do...Any ideas?
>

Norman Jones,

I would kiss your feet if you were in front of me...the logic of that
VB code is quite the interesting bit of code...I am grateful that I
still get answers to some of these hard *** questions even at 5:32 AM
in the morning. You have saved me so much trouble instead of having to
use 300,000 names in a workbook...I sorta wish someone could have
provided this code to me earlier instead of having me run in circles
trying to figure out what was wrong with my workbook. Thanks so much!

-Shaka215


Norman Jones wrote:
> Hi Shaka,
>
> Try:
>
> '=============>>
> Public Sub TesterA01()
> Dim SH As Worksheet
> Dim arr As Variant
> Dim rng As Range
>
> arr = Split(ActiveCell.Value, "!")
>
> Set SH = Sheets(arr(0))
> Set rng = SH.Range(arr(1))
>
> Application.Goto Reference:=rng
>
> End Sub
> '<<=============
>
>
> ---
> Regards,
> Norman
>
>
>
> <Shaka215@gmail.com> wrote in message
> news:1152693389.042004.5190@m79g2000cwm.googlegroups.com...
> > Hello fellow programmers! I am in need of some help trying to get my
> > macros to goto a specific location from a sheet...I'd figure the code
> > would look like this...
> >
> > Application.Goto Reference:="ActiveCell.Value"
> >
> > The problem is I am told my refrence isn't correct..however I have
> > tried to format it the way it works when I use the "Goto" function of
> > Excel (CRTL + G) the format I am using in the active cell is...
> >
> > SheetDB1!B65500
> >
> > When I copy this code and use the goto function it works fine but the
> > macro is failing to realize what it is I am trying to do...Any ideas?
> >