| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Application.Goto Reference:="ActiveCell.Value"??? Whats wrong??
Suppress the quotes!
Application.Goto Reference:=ActiveCell.Value HTH -- AP a écrit dans le message de news: ... 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Application.Goto Reference:="ActiveCell.Value"??? Whats wrong??
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Application.Goto Reference:="ActiveCell.Value"??? Whats wrong??
<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(ActiveCe ll.Value, xlA1, xlR1C1), Len(ActiveCell.Parent.Name) + 6) I doubt this is the best way of achieving this, but it works. NickHK wrote in message ps.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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Application.Goto Reference:="ActiveCell.Value"??? Whats wrong??
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(ActiveCe ll.Value, xlA1, xlR1C1), Len(ActiveCell.Parent.Name) + 6) I doubt this is the best way of achieving this, but it works. NickHK wrote in message ps.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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Application.Goto Reference:="ActiveCell.Value"??? Whats wrong??
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 wrote in message ups.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(ActiveCe ll.Value, xlA1, xlR1C1), Len(ActiveCell.Parent.Name) + 6) I doubt this is the best way of achieving this, but it works. NickHK wrote in message ps.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? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Application.Goto Reference:="ActiveCell.Value"??? Whats wrong??
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 wrote in message ups.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(ActiveCe ll.Value, xlA1, xlR1C1), Len(ActiveCell.Parent.Name) + 6) I doubt this is the best way of achieving this, but it works. NickHK wrote in message ps.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? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Application.Goto Reference:="ActiveCell.Value"??? Whats wrong??
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 wrote in message ups.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 wrote in message ups.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(ActiveCe ll.Value, xlA1, xlR1C1), Len(ActiveCell.Parent.Name) + 6) I doubt this is the best way of achieving this, but it works. NickHK wrote in message ps.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? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Application.Goto Reference:="ActiveCell.Value"??? Whats wrong??
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 wrote in message ups.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(ActiveCe ll.Value, xlA1, xlR1C1), Len(ActiveCell.Parent.Name) + 6) I doubt this is the best way of achieving this, but it works. NickHK wrote in message ps.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? |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Application.Goto Reference:="ActiveCell.Value"??? Whats wrong??
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 wrote in message ups.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(ActiveCe ll.Value, xlA1, xlR1C1), Len(ActiveCell.Parent.Name) + 6) I doubt this is the best way of achieving this, but it works. NickHK wrote in message ps.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? |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Application.Goto Reference:="ActiveCell.Value"??? Whats wrong??
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 wrote in message ps.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? |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Application.Goto Reference:="ActiveCell.Value"??? Whats wrong??
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 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 wrote in message ps.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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Whats the new "Template Wizard with Data Tracking"? | Excel Worksheet Functions | |||
| How to change "automax" to "autosum"? (probablyl wrong terminology) | Excel Discussion (Misc queries) | |||
| whats the big "Page 1" that appears across my spreadsheet? | Excel Discussion (Misc queries) | |||
| Application.Goto reference:="MyCell" | Excel Programming | |||
| Whats wrong with this VBA statement -ActiveCell.FormulaR1C1...? | Excel Programming | |||
Hybrid Mode
