Dynamic range that change variables

Hi
I´ve been searching for a solution for this problem for a long time now and hope that you guys can help me out. I´m just a beginner in VBA so it may be easy.

I have a sheet with a column of Products, to each Product I have different values in other columns on the same row. Rows are deleted and added.
I want to have a userform with a combobox that are populated from the Product column and depending on wich choice the user makes I want to get values from other columns on the same row.
I guess it must be a way to have a dynamic range to populate the combobox and then use the Offset funktion to get values from the other columns but can´t figure it out. Could anybody help me with this ? I would really appreciate it !!
Many thanks :beerchug:

Please attach a sample workbook, by clicking on Go Advanced, scroll down to Manage Attachments and follow the prompts from there.

1) Create a sheet which has a master list of products
2) Create a named range for that list
3) Select the cell you want to have a dropdown. In the Data Validation, select List in the first dropdown and type =RangeName in the second dropdown.

Hope this helps

If it is a user form you can use the RowSource property and give the name of the range. Kindly refer to this site
http://support.microsoft.com/kb/161598

Maybe I wasn´t clear enough, I have no problem populate a combobox from a cell range, the problem is to use an dynamic range to the combobox that refers to the adjacent cells in the row. Hope my file describes it self.
Thanks again!

No one have any ideas on this?? I really been searching a lot on this everywhere and I think it should be an quite easy task, but maybe not?:igiveup:

Code:

---

Private Sub ComboBox1_Change()
    Dim r As Range
    Set r = Columns(1).Find(ComboBox1).Resize(, 3)
    Label6.Caption = r(1)
    Label7.Caption = r(2)
    Label8.Caption = r(3)
End Sub

---

:bow:
Haven´t tried it yet but I´m really happy to get an answer and it seem that simple that I wanted it to be! Will try it , thanks a bunch :friends: !!

:bigdance2 :bigdance2
Thank you so very much !!! Works exactly as i wanted, easy to add and delete rows without changes in the code!!
Thanks again :friends: !!!

How do I mark the thread as solved?

Hi, I tried to use this method but it seems to be some problem with it, some times when I shall make the choice in the combobox I get Runtime error 91, is there an solution to this problem?

B.R / Bengt

What value are you selecting in the combobox?

I have a sheet with products in column A, which I populate the combobox with, to each product I have 16 different values in other columns that belongs to that product. So I want to select a product in the combobox and get values to variables from that choice. What is strange is that sometimes it works fine but sometimes I get the runtime error??

Your example workbook seemed problem free, so post the real book and tell us how to re-create the error.

This is under construction but I made different tests and some works and some not. To populate the combobox, press "Maskin1" or "Maskin2" button
B.R/ Bengt

If you would change

Code:

---

Set r = Columns(1).Find(ComboBox1).Resize(, 17)

---

To

Code:

---

Set r = Sheets("Data1").Columns(1).Find(ComboBox1).Resize(, 17)

---

Would that make a difference?

Looks good so far! Aah, hope it was that simple! Thanks a lot!
B.R/Bengt