Alternative to using a lot of If statements. Key/dictionary?

[Luke777]

Hi,

I have a list of numbers starting in C1 and ending in in an unknown column of row 1.

Each number is a part ID - for example 33 could be a screw, 414 could be a leg of lamb.

I would like code to check the value of each cell in the row, and replace the number for the 'part' name.

I first thought about doing a If then elseif (for unknowns) but quickly realized the typing this all out would a long time and certainly isn't the best way of doing it for a list of parts that stretches into the hundreds.

Is there a method where I could create some sort of key? or any other suggestions? The list of part numbers and part names can exist either on another sheet (same workbook) - I think this approach might be easiest if I use some sort of Match and offset rule?

Thanks

 

[My Aswer Is This]

If the part numbers are in Row(1) starting in C1

Put the Part Numbers in say Column(A) of sheet named "Part_Names")

And a script could run replacing the part numbers with Part names.

So In C1 you have "123"
And in sheet named "Part_Names") you have : "Apples" in range("A1") the part number could be replaced with Part name using a Vba script no need for if.
Provide both sheet names.

 

[My Aswer Is This]

Let's assume your PartNumbers are in row(1)of sheet named "Part Numbers"
Starting in column "C"
And in sheet named "Part Names" you have the part names in column A Starting in Range("A1")
Have the Part Names in column A in order as they should appear in Row(1) of sheet named PartNumbers
Try using this script.

Sub Part_Names()
'Modified  12/20/2021  11:09:33 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim LastColumn As Long
Sheets("Part Numbers").Activate
LastColumn = Sheets("Part Numbers").Cells(1, Columns.Count).End(xlToLeft).Column

    For i = 3 To LastColumn
        Sheets("Part Numbers").Cells(1, i).Value = Sheets("Part Names").Cells(i - 2, 1).Value
    Next
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Another option.
With a sheet of parts like

+Fluff 1.xlsm
	A	B
1	Part number	Part name
2	57	SE152292
3	58	SJ932469
4	62	SE022984
5	74	TQ414570
6	76	SO902060
7	94	SP931096
8	119	SX700968
9	147	SK056479
10	194	SO994846
11	238	SE193432
12	242	SK330786
13	421	NZ191438
14	430	SO917942
15	477	SE101504
16	497	SE125314
Parts

and data like

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1			119	147	421	497	94	194	238	57	477	62	430	58	74	76	242
2
Data

You could use

Sub Luke()
   Dim Cl As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Parts")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Cl.Offset(, 1).Value
      Next Cl
   End With
   With Sheets("Data")
      For Each Cl In .Range("C1", .Cells(1, Columns.Count).End(xlToLeft))
         If Dic.Exists(Cl.Value) Then Cl.Value = Dic(Cl.Value)
      Next Cl
   End With
End Sub