Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change reference style in line of code
Hi Folks!
Application.Goto Reference:="INDIRECT(R1C4)" How do I change the R1C1 reference to an A1 reference? When I use either "D1" or D1 I get errors. Thanks Biff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change reference style in line of code
Application.goto worksheets("sheet1").range("d1")
Qualify as much as you like (include the workbook, too???). Biff wrote: Hi Folks! Application.Goto Reference:="INDIRECT(R1C4)" How do I change the R1C1 reference to an A1 reference? When I use either "D1" or D1 I get errors. Thanks Biff -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change reference style in line of code
Hi Dave!
I don't want to go to cell D1. Let me explain.... (this is very similar to that other post in .Misc about using a drop down with hyperlinks) This is how I did what that poster wants and I was just asking how to change the reference so I could answer their post and not confuse them with the R1C1 reference. You know most people have trouble deciphering R1C1 references. So, cell D1 is my DV drop down. The values in that list are defined names that take me to various places on the sheet. I use a button and some of the code assigned to that button is: Application.Goto Reference:="INDIRECT(R1C4)" I got this code using the macro recorder. So if I select Week_1 from the drop down in cell D1 and then click the button, I'm taken to the cell with the defined name of Week_1. It works PERFECTLY as is but I just wanted to change the R1C1 reference in that line of code to an A1 reference so as not to confuse anyone! Biff "Dave Peterson" wrote in message ... Application.goto worksheets("sheet1").range("d1") Qualify as much as you like (include the workbook, too???). Biff wrote: Hi Folks! Application.Goto Reference:="INDIRECT(R1C4)" How do I change the R1C1 reference to an A1 reference? When I use either "D1" or D1 I get errors. Thanks Biff -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change reference style in line of code
Option Explicit
Sub testme() Dim testRng As Range Dim myCell As Range Set myCell = Worksheets("sheet1").Range("d1") Set testRng = Nothing On Error Resume Next Set testRng = Worksheets("Sheet1").Range(myCell.Value) On Error GoTo 0 If testRng Is Nothing Then Beep Else Application.Goto testRng End If End Sub Biff wrote: Hi Dave! I don't want to go to cell D1. Let me explain.... (this is very similar to that other post in .Misc about using a drop down with hyperlinks) This is how I did what that poster wants and I was just asking how to change the reference so I could answer their post and not confuse them with the R1C1 reference. You know most people have trouble deciphering R1C1 references. So, cell D1 is my DV drop down. The values in that list are defined names that take me to various places on the sheet. I use a button and some of the code assigned to that button is: Application.Goto Reference:="INDIRECT(R1C4)" I got this code using the macro recorder. So if I select Week_1 from the drop down in cell D1 and then click the button, I'm taken to the cell with the defined name of Week_1. It works PERFECTLY as is but I just wanted to change the R1C1 reference in that line of code to an A1 reference so as not to confuse anyone! Biff "Dave Peterson" wrote in message ... Application.goto worksheets("sheet1").range("d1") Qualify as much as you like (include the workbook, too???). Biff wrote: Hi Folks! Application.Goto Reference:="INDIRECT(R1C4)" How do I change the R1C1 reference to an A1 reference? When I use either "D1" or D1 I get errors. Thanks Biff -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change reference style in line of code
That does work but it seems like a "long" way of expressing D1.
Thanks Biff "Dave Peterson" wrote in message ... Option Explicit Sub testme() Dim testRng As Range Dim myCell As Range Set myCell = Worksheets("sheet1").Range("d1") Set testRng = Nothing On Error Resume Next Set testRng = Worksheets("Sheet1").Range(myCell.Value) On Error GoTo 0 If testRng Is Nothing Then Beep Else Application.Goto testRng End If End Sub Biff wrote: Hi Dave! I don't want to go to cell D1. Let me explain.... (this is very similar to that other post in .Misc about using a drop down with hyperlinks) This is how I did what that poster wants and I was just asking how to change the reference so I could answer their post and not confuse them with the R1C1 reference. You know most people have trouble deciphering R1C1 references. So, cell D1 is my DV drop down. The values in that list are defined names that take me to various places on the sheet. I use a button and some of the code assigned to that button is: Application.Goto Reference:="INDIRECT(R1C4)" I got this code using the macro recorder. So if I select Week_1 from the drop down in cell D1 and then click the button, I'm taken to the cell with the defined name of Week_1. It works PERFECTLY as is but I just wanted to change the R1C1 reference in that line of code to an A1 reference so as not to confuse anyone! Biff "Dave Peterson" wrote in message ... Application.goto worksheets("sheet1").range("d1") Qualify as much as you like (include the workbook, too???). Biff wrote: Hi Folks! Application.Goto Reference:="INDIRECT(R1C4)" How do I change the R1C1 reference to an A1 reference? When I use either "D1" or D1 I get errors. Thanks Biff -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change reference style in line of code
If you want to go to the cell listed in cell D1 then
On Error Resume Next Application.Goto Reference:=Range(Range("D1").Value) On Error goto 0 -- Regards, Tom Ogilvy "Biff" wrote in message ... Hi Folks! Application.Goto Reference:="INDIRECT(R1C4)" How do I change the R1C1 reference to an A1 reference? When I use either "D1" or D1 I get errors. Thanks Biff |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change reference style in line of code
That works and is compact at the same time!
Thank, Tom Biff "Tom Ogilvy" wrote in message ... If you want to go to the cell listed in cell D1 then On Error Resume Next Application.Goto Reference:=Range(Range("D1").Value) On Error goto 0 -- Regards, Tom Ogilvy "Biff" wrote in message ... Hi Folks! Application.Goto Reference:="INDIRECT(R1C4)" How do I change the R1C1 reference to an A1 reference? When I use either "D1" or D1 I get errors. Thanks Biff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
xy plot line style change when I select "fixed maximum" | Charts and Charting in Excel | |||
How do I change sheet notation from R1C1 style to A1 style in XL 2 | Setting up and Configuration of Excel | |||
can a1 reference style and r1c1 style be used in same formula? | Excel Worksheet Functions | |||
? change default line style from solid to dotted | Excel Discussion (Misc queries) | |||
Change GETPIVOTDATA cell reference to A1 style | Excel Worksheet Functions |