Change reference style in line of code

[Biff]

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]

Application.goto worksheets("sheet1").range("d1")

Qualify as much as you like (include the workbook, too???).

 

[Biff]

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]

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]

That does work but it seems like a "long" way of expressing D1.

Thanks

Biff

 

[Tom Ogilvy]

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

 

[Biff]

That works and is compact at the same time!

Thank, Tom

Biff