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

[Shaka215]

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]

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

HTH

 

[Shaka215]

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.

 

[Shaka215]

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?

 

[NickHK]

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

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.

<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

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?

 

[NickHK]

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

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.

<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

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]

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
'<<=============

 

[Shaka215]

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

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

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.

<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

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?

 

[Charles Williams]

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

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.

<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

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?

 

[Shaka215]

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

 

[Shaka215]

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

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

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.

<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

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?

 

[NickHK]

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

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

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

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

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?

 

[Shaka215]

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

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

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

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

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

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?