Need some simple code for setting a variable Range

JMH022

Active Member
Joined
Mar 7, 2002
Messages
320
I am sure it is a 'simple' bit of code, but I need to know how to combine a Vlookup with setting a range that can change in size, depending on input from the user.

My example...

Option Group:
O Fruits
O Veggies

A B C D E
1 Fruits Apples Oranges Pineapple Pears
2 Veggies Beans Carrots Corn

I have listed in cell A1 the word "Fruits". Cells B1 through E1 list 4 different types of fruit. When the user selects "Fruits" from the Option Group, the range would be Range(B1:E1) for the 4 different fruit types.

User 2 comes in and selects "Veggies". The range should be Range(B2:D2) for the 3 types of veggies.

I think I need to combine a Vlookup from the cell link of the option group with setting the range, and once the range hits a blank cell I need that to be the end of the range. I just can't seem to work it out.

Any takers on this one?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Here is an uncomplicated crack your problem. What you could do is select A2:A5 and then name that range FRUITS. You would do that by clicking on the name box to the left of the formula bar and typing in the desired name. Then your code would be:

Worksheet_SelectionChange()
Dim Lookup as String
On Error Resume Next
LookupData = InputBox("What would you like to search for?","Enter Search","Fruits")
Range(range(LookupData).Value)
End Sub
 
Upvote 0
What happens if I need to add more 'Fruits to the list (or remove one of the fruits for that matter). If the range hits a blank cell in that range, it will error. If I add more items to the list, it won't be included in the range because it is outside what I set originally. I don't want to have to keep renaming or resetting the range everytime there is a change to the inventory list. I'd really like to be able to select a 'larger-than needed' range so I never outgrow it. Is there a way for the code to recognize that the last item in the list (or the last cell with data) is the end of the range?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top