Cell value = adress. How to select in VBA?

[Gert-Jan]

In my sheet I use a formula for creating an adress. For example: In
Sheet1!A1 there is (as tekst) "Sheet2!A1:A10". In a macro I have to select
that "Sheet2!A1:A10".

I know that there are better ways, but in this case I must use this.

What syntax should I use? Worksheets(Sheet1).Range(A1).Value.Select doesn't
work

 

[Guest]

Gert-Jan
You can not select a worksheet cell on one line. Try

Worksheet("sheet1").Select
Range("A1").select.

As long as you define a range in the macro you can work on that cell without
selecting it.

Dim rng as Range

Worksheets(1).select
set rng = cells(1,1) 'A1 in sheet1

set rng = range(cells(1,1),cells(10,7)) range A1:G10

Alternatively, if you have created a range name in the worksheet Excel
retains the sheet reference and can be called directly in the code.

set rng = Range("MyData")

hope this helps
Peter

 

[Guest]

If your code is in a general module.
s = Worksheets("Sheet1").Range("A1").Value
application.Goto Range(s), True

If it is in a worksheet module, you will need to separate the sheetname from
the cell address

Dim s as String, s1 as String, s2 as String
Dim iloc as Long
s = Worksheets("Sheet1").Range("A1").Value
iloc = Instr(1,s,"!",vbTextcompare)
if iloc <> 0 then
s1 = Left(s,iloc-1)
s2 = Right(s,len(s)-iloc)
else
s1 = "sheet1"
s2 = s
end if
Application.goto Worksheets(s1).Range(s2), True

 

[John]

Hello Gert,

Try:
Worksheets("Sheet1").Range("A1:A10").Select
....or
Worksheets("Sheet1").Range("A1").Select
....depending on what you want.

Best regards

John

 

[Bob Phillips]

Range(Worksheets("Sheet1").Range("A1").Value).Parent.Select
Range(Worksheets("Sheet1").Range("A1").Value).Select


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Dave Peterson]

One more...

Option Explicit
Sub testme()

On Error Resume Next
Application.Goto _
Application.Range(Worksheets("sheet1").Range("a1").Value), _
scroll:=True
If Err.Number <> 0 Then
MsgBox "invalid address"
End If

End Sub