macro - range name to reference contents of another cell

[fulmar57]

Hi

In this macro line, Application.Goto Reference:="rangename"

I need the "rangename" to be given by the contents of another cell eg A1, because it will change

I'm sure it's simple - but not to me. Thanks for your help.

M

 

[paldob]

For named ranges I find it is best to store an additional sheet within the workbook. 3 columns (name of range, sheet name, description)

That way you can find the exact range and sheet. Something like below

Dim NamedRange As String, WorksheetName as String
Dim Fnd As Range, Rng As Range

   NamedRange = Sheets(xxx).Cells(1,1).Value
   Set Fnd = Sheets("NamedRangeInfo").Range("A:A").Find(NamedRange, , , xlWhole, , , False, , False)

   If Fnd Is Nothing Then
     MsgBox "Unable to Find Named Range in the information sheet", vbinformation, "Error..."
     Exit Sub
   Else
     WorksheetName = Fnd.Offset(0,1).Value
   End If

   Set Rng = ThisWorkbook.Sheets(WorksheetName).Range(NamedRange)

   Application.Goto Reference:= Rng
  
   Set Rng = Nothing
   Set Fnd = Nothing

 

[Akuini]

Try:

 Application.Goto Reference:=Range("A1").Value

 

[MARK858]

Deleted as over complicating it, the previous post from Akuini works fine for me.

 

[fulmar57]

That's great. Many thanks

 

[fulmar57]

Actually I have another related question...
That solution worked fine where the range name contained the location as a range name. But I also want to make the macro select a cell (ie go to that cell), where the cell address (eg C45) is given by the content of another cell. The content varies.