Macro/Function question???

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

i am currently making a spreadsheet that will log our equipment that is being repaired and have had some luck using the IF statement in the VB side of it.
i have a drop down list with the equipment in it and when a piece of equipment is selected the script puts the part number into a different cell.

my problem is that, as there is going to be lots of different items entered in to be repaired, the code will be very long winded. i was wondering if there was a shorter way of doing things or if there is actually a preset function already in excel that could do this task.

below is a copy of the code currently doing the task. this is only for 2 items so far to be entered from a possible drop down list of 11 different types of equipment.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("C5").Value = "equip1" Then Range("D5").Value = Range("K7").Value
If Range("C5").Value = "equip2" Then Range("D5").Value = Range("K8").Value
If Range("C5").Value = "equip3" Then Range("D5").Value = Range("K9").Value
If Range("C5").Value = "equip4" Then Range("D5").Value = Range("K10").Value
If Range("C5").Value = "equip5" Then Range("D5").Value = Range("K11").Value
If Range("C5").Value = "equip6" Then Range("D5").Value = Range("K12").Value
If Range("C5").Value = "equip7" Then Range("D5").Value = Range("K13").Value
If Range("C5").Value = "equip8" Then Range("D5").Value = Range("K14").Value
If Range("C5").Value = "equip9" Then Range("D5").Value = Range("K15").Value
If Range("C5").Value = "equip10" Then Range("D5").Value = Range("K16").Value
If Range("C5").Value = "equip11" Then Range("D5").Value = Range("K17").Value
If Range("C5").Value = "" Then Range("D5").Value = ""

If Range("C6").Value = "equip1" Then Range("D6").Value = Range("K7").Value
If Range("C6").Value = "equip2" Then Range("D6").Value = Range("K8").Value
If Range("C6").Value = "equip3" Then Range("D6").Value = Range("K9").Value
If Range("C6").Value = "equip4" Then Range("D6").Value = Range("K10").Value
If Range("C6").Value = "equip5" Then Range("D6").Value = Range("K11").Value
If Range("C6").Value = "equip6" Then Range("D6").Value = Range("K12").Value
If Range("C6").Value = "equip7" Then Range("D6").Value = Range("K13").Value
If Range("C6").Value = "equip8" Then Range("D6").Value = Range("K14").Value
If Range("C6").Value = "equip9" Then Range("D6").Value = Range("K15").Value
If Range("C6").Value = "equip10" Then Range("D6").Value = Range("K16").Value
If Range("C6").Value = "equip11" Then Range("D6").Value = Range("K17").Value
If Range("C6").Value = "" Then Range("D6").Value = ""

End Sub

any ideas?
Cheers,

Nick

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I have an idea and a question

first the idea, HIRE ME!!! :P

ok the question is that it seems that you are using sheet event "SelectionChange"

So the question is event is triggered when you change the selection, in which case it know what cell or cells are selected. So why all these ifs