CODEWITHSUNDEEP
vbaexcel
Giraffe
Giraffe

Reputation: 55

Easiest way to link multiple cells to a combobox selection

I have a combo box with 100 item numbers. I want my user to be able to select a item number, and have multiple cells from a table input into cells on a different worksheet. I could create a massive if/then statement but that would be exhausting. I was hoping someone knew of a more elegant solution.

For example, I could write a nested if/then statement like this:

If ItemNum.Value = "1001" Then

Sheets(10).Range("A2").Value = Sheets(11).Range("F2").Value

Sheets(10).Range("A3").Value = Sheets(11).Range("F3").Value

Sheets(10).Range("A4").Value = Sheets(11).Range("F4").Value

Sheets(10).Range("A5").Value = Sheets(11).Range("F5").Value

elseif ItemNum.Value = "1002" Then

Sheets(10).Range("B2").Value = Sheets(11).Range("G2").Value

Sheets(10).Range("B3").Value = Sheets(11).Range("G3").Value

Sheets(10).Range("B4").Value = Sheets(11).Range("G4").Value

Sheets(10).Range("B5").Value = Sheets(11).Range("G5").Value

Etc. 100 times

Upvotes: 0

Views: 2659

Answers (1)

Robert Co
Robert Co

Reputation: 1715

You don't need VBA. Use Excel formulas: look into MATCH/INDEX, in paticular.

Upvotes: 1

Related Questions