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?
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?