Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro recognition of cell content

0

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?

Answer
Discuss

Answers

0

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.

Discuss

Discussion

Thanks Variatus.  I guess I am still not understanding after selecting a cell how to use its contents as a named range (that applies to another part of the worksheet) so I can actually goto that named range.
suncalf Jul 25, '18 at 10:37 am
I suggest that you create a workbook to illustrate what you want to do and attach it to your original question.
Variatus (rep: 4889) Jul 26, '18 at 4:30 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login