How to use indirect link to select a single cell or range in vba

Question

I just need the code to select the cell, however this cell selects the changes. I have a cell in the book that will determine what cell it should be. Cell A1 contains cell # to be selected.

In this example, cell A1 contains the word "P25", so I want the code below to refer to A1 for the indirect ref cell to P25, so it selects cell P25.

I tried both of these lines separately:

Sub IndirectCellSelect() Sheet.Range(INDIRECT(A1)).Select Range(INDIRECT(A1)).Select End Sub 

I get a Sub error or Function is not detected when it falls into the word INDIRECT

Answer 1

A small change for published code works:

 Range([indirect("a1")]).Select 

but I would suggest trying any of them:

 Sheet.Range(Sheet.Range("A1").Value).Select Range(Range("A1")).Select 

the first is more explicit and recommended in production code.

Answer 2

You can do it differently, but if you want to use your own Excel worksheet in VBA code, you need to do it like this (note that I also adjusted how you reference A1):

 Application.WorksheetFunction.Indirect(Sheets(1).Range("A1")) 

Edit Apologies - I have not tested this. It seems that an indirect function is not available in this context. Instead, try something like this:

 Dim rng as Range Set rng = sheets(1).Range("A1") sheets(1).Range(rng.text).Select 

Answer 3

 Worksheets("list").Sort.SortFields.Add Key:=Range(INDIRECT("I16" & "3" & ":" & "I16" & "5002")) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With Worksheets("list").Sort .SetRange Range("B2:K5002") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin