How to make a macro recognize the contents of a cell. Then use that content as a named range to use in application like goto?
How to make a macro recognize the contents of a cell. Then use that content as a named range to use in application like goto?
In VBA a cell is addressed by the Cells object. Without further specification Cells returns a collection of all cells in the ActiveSheet. You can specify a single cell by adding Row and Column, like Cells(4, 2) to specify B4. Cells(4, "B") also works but is less efficient. A cell is also a range. An alternative way of addressing a cell is to use the Range object. Range("B4") will also specify the same cell. This is the least efficient method.
Once you have specified a cell you have access to all its several dozen properties. One of them is the Value property. A cells Value is the result of the formula it contains, in absence of a formula whatever you entered in it. MsgBox Cells(4, 2).Value will show that value in a mesage box. The Value property is the default property and can therefore be omitted. Doing so leads to complications and misunderstandings. I never omit the Value property in the code I write.
You can assign the Value to a variable. In the case of a range name that would be a string.
Dim RangeName As String
RangeName = Cells(4, 2).Value
You can then use that string to define a range.
Dim MyRange As Range
Set MyRange = Range(RangeName)
The Set instruction is required because the value being assigned is an object. You could also directly use the cell's Value in defining the range: Set MyRange = Range(Cells(4, 2).Value).
There are few limits to what you can do with the above knowledge. If you can imagine it, presume that it can be done. In the attached workbook there is a dropdown with 3 named ranges and code to select the selected range. The code is in the Sheet1 module. It is very, very simple and almost fully explained above. It runs when a change is made. You could choose to run code on double-click or change of another cell, even in another worksheet, or do somethign else with the range. Go ahead and imagine.