2 Issues: Inefficient Array Read/Write Procedure & Compile Error Procedure Too Large

[ehanley]

Hi,

I'm having two issues with my code: the first is, I'm trying to match part numbers I pull in from a database to excel with a part description master; the second is I'm now receiving an error that my procedure is too large to compile.

Background on the first issue: I receive a text output of part numbers and other info from a database that is converted/saved as an excel file. I want to match the part numbers in the output with their proper parts description, which is in a "master" worksheet. To accomplish this, I read the original part numbers into an array, then I proceeded to write a ridiculous number of if/then statements to match the part numbers to the description. I'm aware this is an extremely inefficient way to do this, but I'm a VBA novice so please be kind .

Background on the second issue: I was able to compile the part number and description procedure. After that, I had to perform queries to find counts and sums of certain things I wanted. After I had written several of the count and sum queries I encounter the compile error.

I have attached the code I'm working with, any help is greatly appreciated.


Option Explicit
'define and set variables
Dim i As Integer
Dim j As Integer
Dim myArray() As Variant
Dim countPrintheadAirUS As Integer
Dim countPrintheadAirCA As Integer
Dim sumPrintheadAir As Integer
Dim countPrintheadGroundUS As Integer
Dim countPrintheadGroundCA As Integer
Dim sumPrintheadGround As Integer
Dim countPrintheadOWW As Integer
Dim countPrintheadColorOOW As Integer
Dim sumPrintheadDesc As Integer
Dim countPrintheadOWWrequest As Integer
Dim countPrintheadColorOOWrequest As Integer
Dim sumPrintheadDescRequest As Integer
Dim rowCount As Integer
Dim rowCountToo As Integer
Sub compileTest()
'count the number of rows in sheet 2
With ThisWorkbook.Sheets("Sheet1")
rowCount = .Range("H1", .Range("H" & .Rows.Count).End(xlUp)).Rows.Count
'Debug.Print rowCount

End With
'read the part number column into the array
ReDim myArray(2 To rowCount)
For i = 2 To rowCount
myArray(i) = Cells(i, 8).Value

Next i
Call readArray
Call countAndSum
End Sub
Sub readArray()
For j = 2 To UBound(myArray)
If myArray(j) = "1K1019" Then

Cells(j, 9).Value = "POWER SUPPLY, 108W, AMERICAS/GAR"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1026" Then

Cells(j, 9).Value = "POWER SUPPLY, 108W, WW"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1029" Then

Cells(j, 9).Value = "POWER CORD, AMERICAS/GAR"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1034" Then

Cells(j, 9).Value = "POWER CORD, EAMER (ENGLAND/IRELAND)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1037" Then

Cells(j, 9).Value = "POWER CORD, EAMER"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1040" Then

Cells(j, 9).Value = "POWER CORD, EAMER (ITALY)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1043" Then

Cells(j, 9).Value = "POWER CORD, GAR (AUSTR/NEW ZEALAND)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1085" Then

Cells(j, 9).Value = "BEZEL CONTROL PAN"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1090" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5100, EAMER/GAR (IE)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1094" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5100, AMER/EAMER (F)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1098" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5100, EAMER (G)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1102" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5100, EAMER (I)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1106" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5100, EAMER (ES)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1110" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5100, EAMER (D)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1114" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5300, WW (E)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1121" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5300, AMER/EAMER (F)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1125" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5300, EAMER (G)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1129" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5300, EAMER (I)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1133" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5300, EAMER (S)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1137" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5300, EAMER (D)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1141" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5500, AMERICAS (UE)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1144" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5500, EAMER/GAR (IE)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1148" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5500, AMER/EAMER (F)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1152" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5500, EAMER (G)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1156" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5500, EAMER (I)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1160" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5500, EAMER (ES)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1164" Then

Cells(j, 9).Value = "BEZEL CONT PNL, ES5500, EAMER (D)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1274" Then

Cells(j, 9).Value = "MEDIA"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1286" Then

Cells(j, 9).Value = "CABLE-FAX"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1292" Then

Cells(j, 9).Value = "CABLE-FAX ENG NI NZ"
Cells(j, 10).Value = "Small Item"
ElseIf myArray(j) = "1K1295" Then

Cells(j, 9).Value = "CABLE, FAX, FRANC"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1298" Then

Cells(j, 9).Value = "CABLE, FAX, GERMANY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1301" Then

Cells(j, 9).Value = "CABLE, FAX, ITALY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1304" Then

Cells(j, 9).Value = "CABLE, FAX, NETHERLANDS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1307" Then

Cells(j, 9).Value = "CABLE, FAX, AUSTRALIA"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1568" Then

Cells(j, 9).Value = "PRINT HEAD KIT"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "1K1759" Then

Cells(j, 9).Value = "DUPLEXER ASSY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1769" Then

Cells(j, 9).Value = "POWER SUPPLY 108W INTL"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1775" Then

Cells(j, 9).Value = "POWER CORD END IR"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1776" Then

Cells(j, 9).Value = "POWER CORD EUR"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1777" Then

Cells(j, 9).Value = "POWER CORD IT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1778" Then

Cells(j, 9).Value = "POWER CORD AUS NZ"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1795" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5100 EAM/GAR IE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1796" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5100 AMER/EAM F"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1797" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5100 EAMER G"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1798" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5100 EAMER I"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1799" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5100 EAMER S"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1800" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5100 EAMER D"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1802" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5300 EAM/GAR IE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1803" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5300 AMER/EAM F"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1804" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5300 EAMER G"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1805" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5300 EAMER I"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1806" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5300 EAMER S"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1807" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5300 EAMER D"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1808" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5500 AMER UE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1809" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5500 EAM/GAR IE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1810" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5500 AMER/EAM F"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1811" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5500 EAMER G"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1812" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5500 EAMER I"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1813" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5500 EAMER S"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1814" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5500 EAMER D"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1815" Then

Cells(j, 9).Value = "REFLECTOR FOAM 5500"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1820" Then

Cells(j, 9).Value = "BLUETOOTH MODULE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1845" Then

Cells(j, 9).Value = "FRU CABLE-USB"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1855" Then

Cells(j, 9).Value = "FRU CABLE-FAX E NI NZ"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1860" Then

Cells(j, 9).Value = "FRU CABLE-FAX FR"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1865" Then

Cells(j, 9).Value = "FRU CABLE-FAX GER"
Cells(j, 10).Value = "Small Item"
ElseIf myArray(j) = "1K1870" Then

Cells(j, 9).Value = "FRU CABLE-FAX IT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1875" Then

Cells(j, 9).Value = "FRU CABLE-FAX NE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K1880" Then

Cells(j, 9).Value = "FRU CABLE-FAX AUS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2208" Then

Cells(j, 9).Value = "COVER, CLEAN OUT/TURNAROUND"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2215" Then

Cells(j, 9).Value = "SUPPORT TRAY, PHOTO 5100"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2216" Then

Cells(j, 9).Value = "SUPPORT TRAY, PHOTO 5300"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2217" Then

Cells(j, 9).Value = "SCANNER COVER W/FOAM 5100 5300"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2238" Then

Cells(j, 9).Value = "5100 PRINTER SERVICE RETURN"
Cells(j, 10).Value = "Retn Kit"

ElseIf myArray(j) = "1K2239" Then

Cells(j, 9).Value = "5300 PRINTER SERVICE RETURN"
Cells(j, 10).Value = "Retn Kit"
ElseIf myArray(j) = "1K2240" Then

Cells(j, 9).Value = "5500 PRINTER SERVICE RETURN"
Cells(j, 10).Value = "Retn Kit"

ElseIf myArray(j) = "1K2241" Then

Cells(j, 9).Value = "CLEANOUT COVER"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2274" Then

Cells(j, 9).Value = "ADF INPUT TRAY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2288" Then

Cells(j, 9).Value = "5100 PRINTER WITH PRINT HEAD"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "1K2289" Then

Cells(j, 9).Value = "5300 PRINTER WITH PRINT HEAD"
Cells(j, 10).Value = "Printer HSE"
ElseIf myArray(j) = "1K2290" Then

Cells(j, 9).Value = "5500 PRINTER WITH PRINT HEAD"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "1K2297" Then

Cells(j, 9).Value = "EASYSHARE SOFTWARE CD"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2330" Then

Cells(j, 9).Value = "ESP-3 PRINTER ENGINE"
Cells(j, 10).Value = "Printer HSE"
ElseIf myArray(j) = "1K2331" Then

Cells(j, 9).Value = "POWER SUPPLY, ESP-3, AMERICAS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2332" Then

Cells(j, 9).Value = "POWER SUPPLY, ESP-3, WW"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2337" Then

Cells(j, 9).Value = "POWER CORD ADAPTER, TYPE M"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2426" Then

Cells(j, 9).Value = "SETUP BOOK ESP-3 AMERICAS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2427" Then

Cells(j, 9).Value = "LABEL, PH RETURN INSTRUCTIONS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2428" Then

Cells(j, 9).Value = "SLEEVE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2429" Then

Cells(j, 9).Value = "LABEL, RETURN INSTRUCTIONS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2447" Then

Cells(j, 9).Value = "SETUP BOOK ESP-3 EAMER R/PL/T/IE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2477" Then

Cells(j, 9).Value = "SETUP BOOKLET, 5500, AMERICAS(UE/FC/LS)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2504" Then

Cells(j, 9).Value = "SETUP BOOKLET, 5100"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2506" Then

Cells(j, 9).Value = "CABLE, USB 2.0, HIGHSPEED, A/B"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2524" Then

Cells(j, 9).Value = "SETUP BOOKLET, 5300, EAMER (IE/EF/G/D)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2526" Then

Cells(j, 9).Value = "SETUP BOOKLET, 5500, EAMER (IE/EF/G/D)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2528" Then

Cells(j, 9).Value = "SETUP BOOKLET, 5300, EAMER (I/ES/P)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2530" Then

Cells(j, 9).Value = "SETUP BOOKLET, 5500, EAMER (I/ES/P)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2536" Then

Cells(j, 9).Value = "SETUP BOOKLET, 5300, GAR (IE)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2568" Then

Cells(j, 9).Value = "PRINTHEAD KIT"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "1K2624" Then

Cells(j, 9).Value = "THANK YOU LETTER"
Cells(j, 10).Value = "Small Item"


ElseIf myArray(j) = "1K2633" Then

Cells(j, 9).Value = "SETUP BOOKLET, 5100, EAMER/GAR (IE)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2635" Then

Cells(j, 9).Value = "SETUP BOOKLET, 5100, EAMER (IE/EF/G/D)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2649" Then

Cells(j, 9).Value = "HSE BOX FOR 5100/5300"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2662" Then

Cells(j, 9).Value = "SETUP BOOKLET, 5300, AMERICAS (UE)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2684" Then

Cells(j, 9).Value = "SETUP BOOKLET, 5500, AMERICAS (UE)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2686" Then

Cells(j, 9).Value = "SETUP BOOKLET, 5500, GAR (IE)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2688" Then

Cells(j, 9).Value = "CD SUBASSY V2.0, 5100/5300/550"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2786" Then

Cells(j, 9).Value = "SETUP BOOKLET, 5300, AMERICAS(UE/FC/LS)"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K2871" Then

Cells(j, 9).Value = "INK TANK KIT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K3152" Then

Cells(j, 9).Value = "PRINTHEAD - PACKAGED PALLET"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K3187" Then

Cells(j, 9).Value = "PRINTHEAD"
Cells(j, 10).Value = "Small Item"


ElseIf myArray(j) = "1K4004" Then

Cells(j, 9).Value = "POWER CORD, AMERICAS"
Cells(j, 10).Value = "Small Item"
ElseIf myArray(j) = "1K5100" Then

Cells(j, 9).Value = "ESP-3 AUR PRINTER"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "1K5101" Then

Cells(j, 9).Value = "HSE BOX FOR ESP-3/5"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K5112" Then

Cells(j, 9).Value = "HSE BOX FOR ESP-7"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K5115" Then

Cells(j, 9).Value = "HSE BOX FOR ESP-9"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K5123" Then

Cells(j, 9).Value = "INK CARTRIDGE RETURN KIT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K5124" Then

Cells(j, 9).Value = "LABEL, INK CARTRIDGE RETN INSTR"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K5125" Then

Cells(j, 9).Value = "LABEL, ARS, INK CARTRIDGE RETURN"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "1K5126" Then

Cells(j, 9).Value = "INK CARTRIDGE RETURN INSTRUCTIONS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "2F7499" Then

Cells(j, 9).Value = "ARS LABEL"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "3J9307" Then

Cells(j, 9).Value = "ASSY, DUPLEXER"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "3J9377" Then

Cells(j, 9).Value = "AC POWER CORD, AUSTRALIA / NEW ZEALAND"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "3J9387" Then

Cells(j, 9).Value = "CABLE, FAX, AUSTRIA"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "3J9388" Then

Cells(j, 9).Value = "CABLE, FAX, PORTUGAL / DENMARK"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "3J9390" Then

Cells(j, 9).Value = "CABLE, FAX, SWITZERLAND"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "3J9391" Then

Cells(j, 9).Value = "CABLE, FAX, FINLAND / NORWAY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "3J9392" Then

Cells(j, 9).Value = "CABLE, FAX, SWEDEN"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "3J9394" Then

Cells(j, 9).Value = "CABLE, FAX, BELGIUM"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F4873" Then

Cells(j, 9).Value = "ARS LABEL, PRINTHEAD RETURN"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F4878" Then

Cells(j, 9).Value = "BAG, POLY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9376" Then

Cells(j, 9).Value = "PRINTHEAD KIT"
Cells(j, 10).Value = "Print Head or Kit"
ElseIf myArray(j) = "4F9378" Then

Cells(j, 9).Value = "DUPLEXER"
Cells(j, 10).Value = "Small Item"
ElseIf myArray(j) = "4F9379" Then

Cells(j, 9).Value = "BOX, PRINTHEAD KIT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9380" Then

Cells(j, 9).Value = "PRINTHEAD RETURN KIT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9383" Then

Cells(j, 9).Value = "CARTON, ESP-9"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9384" Then

Cells(j, 9).Value = "CARTON, ESP-7"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9385" Then

Cells(j, 9).Value = "CARTON, 5100/5300"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9387" Then

Cells(j, 9).Value = "RETURN INSTRUCTIONS, PRINTER"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9388" Then

Cells(j, 9).Value = "ARS LABEL, PRINTER RETURN"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9435" Then

Cells(j, 9).Value = "TAPE, HOLD-DOWN, ORANGE, 25MM X 50M"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9436" Then

Cells(j, 9).Value = "BAG - 5100/5300 BAG - 5500"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9443" Then

Cells(j, 9).Value = "CARTON, BROWN BOX ESP-3&5"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9444" Then

Cells(j, 9).Value = "GUIDE,EDGE,LEFT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9445" Then

Cells(j, 9).Value = "COVER,CARD READER,ABS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9446" Then

Cells(j, 9).Value = "LIMIT, MFP"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9447" Then

Cells(j, 9).Value = "COVER, FFC"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9448" Then

Cells(j, 9).Value = "SPRING,SUPPORT,ARM"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9449" Then

Cells(j, 9).Value = "PCBA,MAIN BOARD"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9450" Then

Cells(j, 9).Value = "PCBA,MEDIA CARD"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9451" Then

Cells(j, 9).Value = "ASSY,PRINT ENGINE"
Cells(j, 10).Value = "Printe HSE"

ElseIf myArray(j) = "4F9452" Then

Cells(j, 9).Value = "ASSY,CLEANOUT,5603"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9453" Then

Cells(j, 9).Value = "ASSY,INPUT TRAY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9454" Then

Cells(j, 9).Value = "ASSY,MFP COVER"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9455" Then

Cells(j, 9).Value = "ASSY,MFP BASE COVER"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9456" Then

Cells(j, 9).Value = "SCANNER ASSY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9457" Then

Cells(j, 9).Value = "ASSY, SCANNER LID, WITH WHITE SHEET"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9458" Then

Cells(j, 9).Value = "ARM,SUPPORT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9459" Then

Cells(j, 9).Value = "COVER,PBA"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9460" Then

Cells(j, 9).Value = "COVER,RAIL,LEFT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9461" Then

Cells(j, 9).Value = "COVER,RAIL,RIGHT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9462" Then

Cells(j, 9).Value = "SPRING,ENCODER STRIP"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9463" Then

Cells(j, 9).Value = "WIPER,G2,EPDM,ROHS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9464" Then

Cells(j, 9).Value = "FOAM,CAP,HDPE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9465" Then

Cells(j, 9).Value = "SCREW TP32-3.0+6PF-MI, ROHS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9466" Then

Cells(j, 9).Value = "BASE,CAP,ABS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9467" Then

Cells(j, 9).Value = "N-S SCREW,M3.0*0.5+4B-ZNC"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9468" Then

Cells(j, 9).Value = "SCREW,M3.0*0.5+6PF-NI,ROHS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9469" Then

Cells(j, 9).Value = "SCREW,TP2NC-3.0*1.06+8BF-ZN"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9470" Then

Cells(j, 9).Value = "SCREW,TP2NC-3.0*1.06+8BF-ZN"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9471" Then

Cells(j, 9).Value = "SCREW,TP2NC-3.0*1.06+8BF-NIB"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9472" Then

Cells(j, 9).Value = "ENCODER STRIP,AS400 FILM"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9473" Then

Cells(j, 9).Value = "ENCODER WHEEL"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9474" Then

Cells(j, 9).Value = "ASSY,MAINTENANCE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9475" Then

Cells(j, 9).Value = "BLOCK,MS,INK"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9476" Then

Cells(j, 9).Value = "PAD, FRICTION"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9477" Then

Cells(j, 9).Value = "ENCDR, DISC"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9478" Then

Cells(j, 9).Value = "LOCK, WIPER"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9480" Then

Cells(j, 9).Value = "SCREW, M2X7, PLASTITE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9481" Then

Cells(j, 9).Value = "ABSORBENT, MS, SCRAPER"
Cells(j, 10).Value = "Small Item"
ElseIf myArray(j) = "4F9482" Then

Cells(j, 9).Value = "SHEET, WHITE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9483" Then

Cells(j, 9).Value = "ABSORBENT, CAP"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9484" Then

Cells(j, 9).Value = "SCREW, M3 X 12, PLASTITE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9485" Then

Cells(j, 9).Value = "SCREW, M3X6, P-TYPE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9486" Then

Cells(j, 9).Value = "PRINTER RETURN KIT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9487" Then

Cells(j, 9).Value = "MYLAR LCD BETTER"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9488" Then

Cells(j, 9).Value = "SCREW, M3X6, TORX, PWH, MACH,BLUE ZINC"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9489" Then

Cells(j, 9).Value = "SCREW, M3X16, TORX, PH, TF, BLUE ZINC"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9490" Then

Cells(j, 9).Value = "WIPER BLADE, MS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9491" Then

Cells(j, 9).Value = "SCREW, M3X5, PH, MACH, TORX, CLEAR ZINC"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9492" Then

Cells(j, 9).Value = "ABSORBER, PLATEN, PRINTZONE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9493" Then

Cells(j, 9).Value = "CAP SEAL WITH INSERT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9494" Then

Cells(j, 9).Value = "SCREW, M2.5X15, TF, TORX, PLASTITE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9495" Then

Cells(j, 9).Value = "ADF DOCUMENT TRAY, ES5500"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9496" Then

Cells(j, 9).Value = "PAPER TRAY,UPPER ASSY,SUPP 5100"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9497" Then

Cells(j, 9).Value = "PAPER TRAY,UPPER ASSY,SUPP 5300/5500"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9498" Then

Cells(j, 9).Value = "ASSY,COVER,SCANNER"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4F9499" Then

Cells(j, 9).Value = "CABLE, FAX,STD, ES5500"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4J6832" Then

Cells(j, 9).Value = "PRINTHEAD RETURN INSTRUCTIONS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4J6833" Then

Cells(j, 9).Value = "PRINTHEAD INSTALLATION DOCUMENT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4J6849" Then

Cells(j, 9).Value = "PRINTER RETURN INSTRUCTIONS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "4J7177" Then

Cells(j, 9).Value = "PRINTER RETURN INSTRUCTIONS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "6J2138" Then

Cells(j, 9).Value = "PRINT HEAD"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "6J2162" Then

Cells(j, 9).Value = "PRINTHEAD"
Cells(j, 10).Value = "Print Head or Kit"
ElseIf myArray(j) = "7B7795" Then

Cells(j, 9).Value = "PRINTER RETURN INSTRUCTIONS FOR CANADA"
Cells(j, 10).Value = "Small Item"
ElseIf myArray(j) = "AUR1780-1" Then

Cells(j, 9).Value = "REPLACEMENT INSTRUCTION 5100/5300/5500"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "AUR1780-21" Then

Cells(j, 9).Value = "INSTRUCTION SHEET - PRINTHEAD ONLY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "AUR1780-3" Then

Cells(j, 9).Value = "INSTRUCTION SHEET - CLEANOUT/T"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "AUR1780-4" Then

Cells(j, 9).Value = "INSTRUCTION SHEET - PAPER TRAY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "AUR1780-5" Then

Cells(j, 9).Value = "INSTRUCTION SHEET - BEZEL CONT 5100/5300"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "AUR1780-6" Then

Cells(j, 9).Value = "INSTRUCTION SHEET - SCANNER CO"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "AUR1780-7" Then

Cells(j, 9).Value = "INSTRUCTION SHEET-INSTALL INK CARTRIDGE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "AUR1780-8" Then

Cells(j, 9).Value = "INSTRUCTION SHEET - ASSY, DUPL"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "AUR1780-9" Then

Cells(j, 9).Value = "INSTRUCTION SHEET - PRINTHEAD KIT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "AUR1782-1" Then

Cells(j, 9).Value = "INSTRUCTION SHEET - BEZEL CONT 5500"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "AUR1782-2" Then

Cells(j, 9).Value = "INSTRUCTION SHEET - ADF DOCUMENT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "AUR1782-3" Then

Cells(j, 9).Value = "INSTRUCTION SHEET - SHEET, WHITE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1055722" Then

Cells(j, 9).Value = "ESP C315 RETAIL PRINTER"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1068105" Then

Cells(j, 9).Value = "SAMPLE PACK PAPER 4X6 5SHT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1079094" Then

Cells(j, 9).Value = "ESP 7250 RETAIL PRINTER"
Cells(j, 10).Value = "Printer HSE"
ElseIf myArray(j) = "SP1131267" Then

Cells(j, 9).Value = "ESP 3.2 HSE/RETAIL PRINTER"
Cells(j, 10).Value = "Printer HSE"
ElseIf myArray(j) = "SP1189265" Then

Cells(j, 9).Value = "PAPER 8.5X11 PREM 10SHT/PK"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1194802" Then

Cells(j, 9).Value = "5100 PRINTER CLASS B"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1252972" Then

Cells(j, 9).Value = "ESP-7 PRINTER RETAIL"
Cells(j, 10).Value = "Printer HSE"
ElseIf myArray(j) = "SP1326479" Then

Cells(j, 9).Value = "PANTHER HERO 5.1 RETAIL / HSE PRINTER"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1349471" Then

Cells(j, 9).Value = "ESP-9 PRINTER RETAIL E/F"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1465533" Then

Cells(j, 9).Value = "SP1465533"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1488881" Then

Cells(j, 9).Value = "ESP 5210 RETAIL PRINTER"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1533561" Then

Cells(j, 9).Value = "TOPAZ HERO 6.1 RETAIL / HSE PRINTER"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1600105" Then

Cells(j, 9).Value = "5500 PRINTER RETAIL"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1629054" Then

Cells(j, 9).Value = "ESP-3 PRINTER RETAIL"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1641877" Then

Cells(j, 9).Value = "5100 AIO PRINTER ENG/FR"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1695485" Then

Cells(j, 9).Value = "PAPER 4X6 ULTRA 10SHT/PK"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1735893" Then

Cells(j, 9).Value = "ESP-7 PRINTER RETAIL E/F"
Cells(j, 10).Value = "Printer HSE"
ElseIf myArray(j) = "SP1743327" Then

Cells(j, 9).Value = "PHOTO PAPER, 100 SHEET BOX"
Cells(j, 10).Value = "Small Item"
ElseIf myArray(j) = "SP1749456" Then

Cells(j, 9).Value = "ESP-5250 RETAIL PRINTER"
Cells(j, 10).Value = "Printer HSE"
ElseIf myArray(j) = "SP1756931" Then

Cells(j, 9).Value = "AIO PRNTR / ESP-3 AMER E/F/S/P"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1774488" Then

Cells(j, 9).Value = "ESP 6150 RETAIL PRINTER"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1804392" Then

Cells(j, 9).Value = "ESP 7250 CLASS B RETAIL PRINTER"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1805209" Then

Cells(j, 9).Value = "ESP 3250 CLASS B RETAIL PRINTER"
Cells(j, 10).Value = "Printer B"
ElseIf myArray(j) = "SP1850601" Then

Cells(j, 9).Value = "HERO 4.2 HSE / RETAIL PRINTER"
Cells(j, 10).Value = "Printer HSE"
ElseIf myArray(j) = "SP1985613" Then

Cells(j, 9).Value = "ESP C310 RETAIL PRINTER"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1K1274" Then

Cells(j, 9).Value = "MEDIA"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K1740" Then

Cells(j, 9).Value = "5100 PRINTER AUR CLASS B"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K1746" Then

Cells(j, 9).Value = "5300 PRINTER AUR CLASS B"
Cells(j, 10).Value = "Printer B"
ElseIf myArray(j) = "SP1K1747" Then

Cells(j, 9).Value = "5500 PRINTER AUR CLASS B"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K1748" Then

Cells(j, 9).Value = "PRINTHEAD KIT"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "SP1K1753" Then

Cells(j, 9).Value = "BLACK INK CARTRIDGE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K1758" Then

Cells(j, 9).Value = "COLOR INK CARTRIDGE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K1764" Then

Cells(j, 9).Value = "POWER SUPPLY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K1770" Then

Cells(j, 9).Value = "POWER CORD"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K1790" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5100 AMER UE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K1801" Then

Cells(j, 9).Value = "BEZEL,CONTROL PANEL,ES5300 AMER UE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K1825" Then

Cells(j, 9).Value = "8.5X11 MEDIA PACK"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K1835" Then

Cells(j, 9).Value = "4X6 MEDIA PACK"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K2241" Then

Cells(j, 9).Value = "TURNAROUND/CLEANOUT ASSY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K2275" Then

Cells(j, 9).Value = "5100 PRINTER CR"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1K2276" Then

Cells(j, 9).Value = "5300 PRINTER CR"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1K2277" Then

Cells(j, 9).Value = "5500 PRINTER CR"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1K2330" Then

Cells(j, 9).Value = "ESP-3 PRINTER"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1K2568" Then

Cells(j, 9).Value = "PRINTHEAD KIT"
Cells(j, 10).Value = "Print Head or Kit"
ElseIf myArray(j) = "SP1K2703" Then

Cells(j, 9).Value = "CD, SIERRA PRINT ARTIST"
Cells(j, 10).Value = "Small Item"
ElseIf myArray(j) = "SP1K2993" Then

Cells(j, 9).Value = "ESP-3 PRINTER CR"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1K2994" Then

Cells(j, 9).Value = "ESP-5 PRINTER CR"
Cells(j, 10).Value = "Printer HSE"
ElseIf myArray(j) = "SP1K2995" Then

Cells(j, 9).Value = "ESP-7 PRINTER CR"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1K2996" Then

Cells(j, 9).Value = "ESP-9 PRINTER CR"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP1K3132" Then

Cells(j, 9).Value = "BLACK INK CARTRIDGE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K3133" Then

Cells(j, 9).Value = "PRINT HEAD"
Cells(j, 10).Value = "Print Head or Kit"
ElseIf myArray(j) = "SP1K3141" Then

Cells(j, 9).Value = "COLOR INK CARTRIDGE"
Cells(j, 10).Value = "Small Item"
ElseIf myArray(j) = "SP1K3156" Then

Cells(j, 9).Value = "COLOR INK CARTRIDGE"
Cells(j, 10).Value = "Small Item"
ElseIf myArray(j) = "SP1K3157" Then

Cells(j, 9).Value = "BLACK INK CARTRIDGE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K3177" Then

Cells(j, 9).Value = "BLACK INK CARTRIDGE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K3178" Then

Cells(j, 9).Value = "COLOR INK CARTRIDGE"
Cells(j, 10).Value = "Small Item"
ElseIf myArray(j) = "SP1K3187" Then

Cells(j, 9).Value = "PRINTHEAD"
Cells(j, 10).Value = "Print Head or Kit"
ElseIf myArray(j) = "SP1K3196" Then

Cells(j, 9).Value = "PRINTHEAD"
Cells(j, 10).Value = "Print Head or Kit"
ElseIf myArray(j) = "SP1K3197" Then

Cells(j, 9).Value = "PRINTHEAD G1.5"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "SP1K3198" Then

Cells(j, 9).Value = "PRINTHEAD G1.5"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "SP1K3587" Then

Cells(j, 9).Value = "PRINTHEAD G1.5"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "SP1K3640" Then

Cells(j, 9).Value = "TETRIS PRINTHEAD"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "SP1K3648" Then

Cells(j, 9).Value = "BLACK INK CARTRIDGE, 30B"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K3680" Then

Cells(j, 9).Value = "TETRIS GTO PRINTHEAD"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "SP1K4239" Then

Cells(j, 9).Value = "ESP 7/7250 SCANNER LID"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4258" Then

Cells(j, 9).Value = "ENCODER STRIP"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4427" Then

Cells(j, 9).Value = "SCANNER ASSY, ESP 4150, 6150 & SCANNER BASE, ESP 7250"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4454" Then

Cells(j, 9).Value = "RUBBER FEET"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4455" Then
Cells(j, 9).Value = "SPRING"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4480" Then

Cells(j, 9).Value = "LATCH"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4502" Then

Cells(j, 9).Value = "COVER, CABLE BACK"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4506" Then

Cells(j, 9).Value = "COVER, CABLE FRONT"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4519" Then

Cells(j, 9).Value = "MAIN TRAY, PAPER, ESP 4150, 6150"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4521" Then

Cells(j, 9).Value = "PHOTO TRAY, PAPER ESP 7250"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4530" Then

Cells(j, 9).Value = "ADF INPUT TRAY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4539" Then

Cells(j, 9).Value = "SCANNER LID, ESP 7250"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4657" Then

Cells(j, 9).Value = "CONTROL PANEL, ESP 4150"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4665" Then

Cells(j, 9).Value = "FAÇADE, ESP 4150, 6150"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4746" Then

Cells(j, 9).Value = "CONTROL PANEL, ESP 6150"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4781" Then

Cells(j, 9).Value = "PAPER FRAME, NARROW"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4782" Then

Cells(j, 9).Value = "PAPER FRAME, WIDE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K4968" Then

Cells(j, 9).Value = "SENSOR, ACCESS COVER"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5048" Then

Cells(j, 9).Value = "PRINTCREATION CD"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5052" Then

Cells(j, 9).Value = "ADVENT AIO SOFTWARE CD"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5100" Then

Cells(j, 9).Value = "ESP-3 PRINTER AUR CLASS A"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K5101" Then

Cells(j, 9).Value = "ESP-3 PRINTER OUTBOUND"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K5102" Then

Cells(j, 9).Value = "ESP-5 PRINTER AUR CLASS A"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K5104" Then

Cells(j, 9).Value = "HSE BOX FOR 5500"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5105" Then

Cells(j, 9).Value = "ESP 3 PRINTER AUR CLASS B"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K5106" Then

Cells(j, 9).Value = "ESP 5 PRINTER AUR CLASS B"
Cells(j, 10).Value = "Printer B"
ElseIf myArray(j) = "SP1K5107" Then

Cells(j, 9).Value = "5100 PRINTER AUR CLASS A"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K5108" Then

Cells(j, 9).Value = "5300 PRINTER AUR CLASS A"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K5109" Then

Cells(j, 9).Value = "5500 PRINTER AUR CLASS A"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K5110" Then

Cells(j, 9).Value = "ESP-7 PRINTER AUR CLASS A"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K5111" Then

Cells(j, 9).Value = "ESP-7 PRINTER AUR CLASS B"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K5113" Then

Cells(j, 9).Value = "ESP-9 PRINTER AUR CLASS A"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K5114" Then

Cells(j, 9).Value = "ESP-9 PRINTER AUR CLASS B"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K5134" Then

Cells(j, 9).Value = "ESP-3250 PRINTER AUR CLASS A"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K5135" Then

Cells(j, 9).Value = "ESP-5250 PRINTER AUR CLASS A"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K5136" Then

Cells(j, 9).Value = "ESP-3250 PRINTER AUR CLASS B"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K5137" Then

Cells(j, 9).Value = "ESP-5250 PRINTER AUR CLASS B"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K5139" Then

Cells(j, 9).Value = "HSE BOX ESP 3250/5250"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5147" Then

Cells(j, 9).Value = "ESP 4150 CLASS B, AUR"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K5148" Then

Cells(j, 9).Value = "ESP 6150 CLASS B, AUR"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K5149" Then

Cells(j, 9).Value = "ESP 7250 CLASS B, AUR"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K5156" Then

Cells(j, 9).Value = "HSE BOX ESP 4150"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5157" Then

Cells(j, 9).Value = "HSE BOX ESP 6150 & HSE BOX ESP 6150, 9250"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5158" Then

Cells(j, 9).Value = "HSE BOX ESP 7250"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5160" Then

Cells(j, 9).Value = "BLACK INK CARTRIDGE, XL"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5161" Then

Cells(j, 9).Value = "PRINTER RETURN KIT, ELF"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5162" Then

Cells(j, 9).Value = "PRINTER RETURN INSTRUCTIONS, ELF"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5163" Then

Cells(j, 9).Value = "ARS LABEL, ELF"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5167" Then

Cells(j, 9).Value = "PRINTHEAD KIT, OWW"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "SP1K5168" Then

Cells(j, 9).Value = "PRINTHEAD AND INK KIT, OOW"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "SP1K5169" Then

Cells(j, 9).Value = "PRINTHEAD KIT, DELL"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "SP1K5170" Then

Cells(j, 9).Value = "DELL BLACK INK CARTRIDGE"
Cells(j, 10).Value = "Small Item"
ElseIf myArray(j) = "SP1K5171" Then

Cells(j, 9).Value = "DELL COLOR INK CARTRIDGE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5199" Then

Cells(j, 9).Value = "HSE BOX, ESP C310 PRINTER"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5200" Then

Cells(j, 9).Value = "HSE BOX ESP C110/C310/C315"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5202" Then

Cells(j, 9).Value = "HSE BOX ESP 2150 / 2170"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5206" Then

Cells(j, 9).Value = "PRINTHEAD KIT, FOUR COLOR"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "SP1K5214" Then

Cells(j, 9).Value = "PRINTER RETURN KIT, ELF"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5215" Then

Cells(j, 9).Value = "ARS LABEL, ELF"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5216" Then

Cells(j, 9).Value = "HSE BOX HERO 5.1"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5217" Then

Cells(j, 9).Value = "HSE BOX HERO 6.1"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5218" Then

Cells(j, 9).Value = "HSE BOX HERO 7.1"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5219" Then

Cells(j, 9).Value = "HSE BOX HERO 9.1"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5223" Then

Cells(j, 9).Value = "PRINTHEAD KIT, FOUR COLOR OOW"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "SP1K5224" Then

Cells(j, 9).Value = "PRINTHEAD KIT, FOUR COLOR W/INK OOW"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "SP1K5229" Then

Cells(j, 9).Value = "WARRANTY PRINTHEAD KIT - FOUR COLOR PNP"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "SP1K5232" Then

Cells(j, 9).Value = "HSE BOX HERO 4.2"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5517" Then

Cells(j, 9).Value = "TISSUE PAPER, SCANNER GLASS, ESP 4150"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5564" Then

Cells(j, 9).Value = "PAPER TRAY, ESP 3250 PRINTER"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5667" Then

Cells(j, 9).Value = "POWER SUPPLY, ALL ESP PRINTERS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5849" Then

Cells(j, 9).Value = "ESP 4150 CLASS A, AUR"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K5850" Then

Cells(j, 9).Value = "ESP 6150 CLASS A, AUR"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K5864" Then

Cells(j, 9).Value = "OFFICE READY CD"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K5886" Then

Cells(j, 9).Value = "ESP 7250 CLASS A, AUR"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K5969" Then

Cells(j, 9).Value = "ESP 9250 CLASS A, AUR PRINTER"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K5970" Then

Cells(j, 9).Value = "ESP 9250 CLASS B, AUR PRINTER"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K5972" Then

Cells(j, 9).Value = "ESP C315 CLASS A AUR, PRINTER"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K5973" Then

Cells(j, 9).Value = "ESP C315 CLASS B AUR, PRINTER"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K5975" Then

Cells(j, 9).Value = "ESP C310 CLASS A AUR, PRINTER"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K5976" Then

Cells(j, 9).Value = "ESP C310 CLASS B AUR, PRINTER"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K5989" Then

Cells(j, 9).Value = "ESP 5210 CLASS B, AUR PRINTER"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K6355" Then

Cells(j, 9).Value = "HARNESS ***'Y, M/B-WIFI/B 5W 380"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K6532" Then

Cells(j, 9).Value = "COVER, BASE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K6751" Then

Cells(j, 9).Value = "ESP C110/310/315 PAPER INPUT TRAY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7000" Then

Cells(j, 9).Value = "DUPLEXER, HERO 6.1"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7005" Then

Cells(j, 9).Value = "PRINT ENGINE, ESP 6150"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7209" Then

Cells(j, 9).Value = "PRINT ENGINE, ESP 4150"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7212" Then

Cells(j, 9).Value = "CLEAN OUT COVER, ESP 6150"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7229" Then

Cells(j, 9).Value = "FAÇADE, ESP 7250"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7249" Then

Cells(j, 9).Value = "CONTROL PANEL, ESP 7250"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7253" Then

Cells(j, 9).Value = "MAIN TRAY, PAPER, ESP 7250"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7255" Then

Cells(j, 9).Value = "PRINT ENGINE, ESP 7250"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7257" Then

Cells(j, 9).Value = "SCANNER ASSY, ESP 7250"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7260" Then

Cells(j, 9).Value = "DUPLEXER , ESP 4150, 7250"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7266" Then

Cells(j, 9).Value = "OUTPUT TRAY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7313" Then

Cells(j, 9).Value = "POWER SUPPLY, C SERIES PRINTERS"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7378" Then

Cells(j, 9).Value = "POWER CORD FOR C SERIES POWER SUPPLY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7411" Then

Cells(j, 9).Value = "ESP 2150/2170 PAPER INPUT TRAY COVER"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7413" Then

Cells(j, 9).Value = "ESP 2150/2170 PAPER INPUT TRAY COVER"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7602" Then

Cells(j, 9).Value = "ESP-2170 CLASS A AUR PRINTER"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K7603" Then

Cells(j, 9).Value = "ESP 2150 CLASS B AUR PRINTER"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K7650" Then

Cells(j, 9).Value = "ESP 2170 CLASS B AUR PRINTER"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K7660" Then

Cells(j, 9).Value = "POWER SUPPLY C SERIES PRINTERS ONLY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7720" Then

Cells(j, 9).Value = "ESP 2150/2170 ADF INPUT TRAY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K7916" Then

Cells(j, 9).Value = "HARNESS ASSY, WIFI-M/B 5W L360"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K8004" Then

Cells(j, 9).Value = "HARRY POTTER 2 DESIGN GALLERY CD"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K8005" Then

Cells(j, 9).Value = "POWER SUPPLY 18 WATT 2 WIRE"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K8023" Then

Cells(j, 9).Value = "JAGUAR HERO 7.1 CLASS A, AUR PRINTER"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K8024" Then

Cells(j, 9).Value = "HERO 7.1 CLASS B AUR "
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K8025" Then

Cells(j, 9).Value = "JAGUAR KING HERO 9.1 CLASS A, AUR PRINTER"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K8026" Then

Cells(j, 9).Value = "HERO 9.1 CLASS B, AUR"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K8027" Then

Cells(j, 9).Value = "TOPAZ HERO 6.1 CLASS A, AUR PRINTER"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K8028" Then

Cells(j, 9).Value = "HERO 6.1 CLASS B, AUR"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K8029" Then

Cells(j, 9).Value = "PANTHER HERO 5.1 CLASS A, AUR PRINTER"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K8122" Then

Cells(j, 9).Value = "TRON 99 HERO 3.1 CLASS A, AUR PRINTER"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K8123" Then

Cells(j, 9).Value = "HERO 3.1, CLASS B AUR"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K8295" Then

Cells(j, 9).Value = "ESP 2150 PAPER OUTPUT TRAY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K8296" Then

Cells(j, 9).Value = "ESP 2150/2170 PAPER OUTPUT TRAY"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K8418" Then

Cells(j, 9).Value = "ADF INPUT TRAY, HERO 9.1"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K8436" Then

Cells(j, 9).Value = "SCANNER LID ASSEMBLY, HERO 7.1"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K8436" Then

Cells(j, 9).Value = "SCANNER LID ASSEMBLY, HERO 7.1"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K8578" Then

Cells(j, 9).Value = "ESP 3.2 CLASS A AUR PRINTER"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP1K8580" Then

Cells(j, 9).Value = "ESP 3.2 CLASS B AUR PRINTER"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP1K8838" Then

Cells(j, 9).Value = "PAPER INPUT TRAY, HERO 3.1"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K8879" Then

Cells(j, 9).Value = "DUPLEXER ASSY, HERO 7.1 and 9.1"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K9093" Then

Cells(j, 9).Value = "ADF INPUT TRAY LID, HERO 6.1"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K9143" Then

Cells(j, 9).Value = "ADF OUTPUT PAPER STOPPER, HERO 6.1"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K9147" Then

Cells(j, 9).Value = "ADF INPUT TRAY LID EXTENDER, HERO 6.1"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K9296" Then

Cells(j, 9).Value = "SCANNER LID ASSY HERO 5.1"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP1K9430" Then

Cells(j, 9).Value = "DUPLEXER, HERO 5.1"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP3J8965" Then

Cells(j, 9).Value = "BLACK INK TANK"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP3J8966" Then

Cells(j, 9).Value = "COLOR INK TANK"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP4F7695" Then

Cells(j, 9).Value = "BOX 24X18X12"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP4K3041" Then

Cells(j, 9).Value = "HERO 4.2 CLASS B AUR PRINTER"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP4K3066" Then

Cells(j, 9).Value = "HERO 4.2 CLASS A AUR PRINTER"
Cells(j, 10).Value = "Printer A"

ElseIf myArray(j) = "SP4K6512" Then

Cells(j, 9).Value = "PRINTHEAD TETRIS 2G DESS."
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "SP4K6513" Then

Cells(j, 9).Value = "PRINTHEAD BLACKJACK 2G DESS."
Cells(j, 10).Value = "Print Head or Kit"
ElseIf myArray(j) = "SP6J2234" Then

Cells(j, 9).Value = "PRINTHEAD"
Cells(j, 10).Value = "Print Head or Kit"
ElseIf myArray(j) = "SP6J2236" Then

Cells(j, 9).Value = "PRINTHEAD G1.5"
Cells(j, 10).Value = "Print Head or Kit"
ElseIf myArray(j) = "SP6J5844" Then

Cells(j, 9).Value = "COLOR INK CARTRIDGE- 30C"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP6J5862" Then

Cells(j, 9).Value = "COLOR INK CARTRIDGE- 30CXL"
Cells(j, 10).Value = "Small Item"
ElseIf myArray(j) = "SP6J5864" Then

Cells(j, 9).Value = "BLACK INK CARTRIDGE, 30BXL"
Cells(j, 10).Value = "Small Item"

ElseIf myArray(j) = "SP7433394" Then

Cells(j, 9).Value = "10 SERIES PRINTHEAD KIT FOR RETAIL"
Cells(j, 10).Value = "Print Head or Kit"
ElseIf myArray(j) = "SP7433402" Then

Cells(j, 9).Value = "30 SERIES PRINTHEAD KIT FOR RETAIL"
Cells(j, 10).Value = "Print Head or Kit"

ElseIf myArray(j) = "SP8019630" Then

Cells(j, 9).Value = "JAGUAR KING HERO 9.1 RETAIL / HSE PRINTER"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP8184764" Then

Cells(j, 9).Value = "ESP-5 PRINTER CLASS B"
Cells(j, 10).Value = "Printer B"
ElseIf myArray(j) = "SP8287419" Then

Cells(j, 9).Value = "ESP-7 PRINTER CLASS B"
Cells(j, 10).Value = "Printer B"
ElseIf myArray(j) = "SP8290207" Then

Cells(j, 9).Value = "5100 PRINTER RETAIL"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP8294548" Then

Cells(j, 9).Value = "ESP 2170 RETAIL / HSE PRINTER"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP8385452" Then

Cells(j, 9).Value = "5500 PRINTER CLASS B"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP8424996" Then

Cells(j, 9).Value = "ESP 4150 CLASS B RETAIL PRINTER"
Cells(j, 10).Value = "Printer B"
ElseIf myArray(j) = "SP8437477" Then

Cells(j, 9).Value = "ESP-9 PRINTER RETAIL"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP8477770" Then

Cells(j, 9).Value = "ESP 5250 CLASS B RETAIL PRINTER"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP8491169" Then

Cells(j, 9).Value = "ESP-2150 RETAIL / HSE PRINTER"
Cells(j, 10).Value = "Printer HSE"
ElseIf myArray(j) = "SP8509408" Then

Cells(j, 9).Value = "ESP-5 PRINTER RETAIL"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP8665424" Then

Cells(j, 9).Value = "5300 PRINTER RETAIL"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP8804056" Then

Cells(j, 9).Value = "5300 PRINTER RETAIL"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP8880189" Then

Cells(j, 9).Value = "ESP 9250 RETAIL PRINTER"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP8911414" Then

Cells(j, 9).Value = "ESP 6150 CLASS B RETAIL PRINTER"
Cells(j, 10).Value = "Printer B"

ElseIf myArray(j) = "SP8918765" Then

Cells(j, 9).Value = "ESP-3 PRINTER CLASS B"
Cells(j, 10).Value = "Printer B"
ElseIf myArray(j) = "SP8946139" Then

Cells(j, 9).Value = "ESP-3250 RETAIL PRINTER- EXCH"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP8950743" Then

Cells(j, 9).Value = "JAGUAR HERO 7.1 RETAIL / HSE PRINTER"
Cells(j, 10).Value = "Printer HSE"

ElseIf myArray(j) = "SP8958688" Then

Cells(j, 9).Value = "TRON 99 HERO 3.1 RETAIL / HSE PRINTER"
Cells(j, 10).Value = "Printer HSE"

Else

Cells(j, 9).Value = "ESP-9 PRINTER CLASS "
Cells(j, 10).Value = "Printer B"

End If

Next j
End Sub
Sub countAndSum()
For i = 2 To rowCount
If Cells(i, 4).Value = "US" Then

If Cells(i, 7).Value = "Air" Then

If Cells(i, 10).Value = "Print Head or Kit" Then

countPrintheadAirUS = countPrintheadAirUS + 1

End If

End If

End If

Next i
'Debug.Print countPrintheadAirUS
'count the printheads shipped by air to canada
For i = 2 To rowCount
If Cells(i, 4).Value = "CA" Then

If Cells(i, 7).Value = "Air" Then

If Cells(i, 10).Value = "Print Head or Kit" Then

countPrintheadAirCA = countPrintheadAirCA + 1

End If

End If

End If

Next i
'Debug.Print countPrintheadAirCA
'sum all printheads shipped via air
sumPrintheadAir = countPrintheadAirUS + countPrintheadAirCA
'Debug.Print sumPrintheadAir
'count the printheads shipped by ground within the us
For i = 2 To rowCount
If Cells(i, 4).Value = "US" Then

If Cells(i, 7).Value = "Ground" Then

If Cells(i, 10).Value = "Print Head or Kit" Then

countPrintheadGroundUS = countPrintheadGroundUS + 1

End If

End If

End If

Next i
'Debug.Print countPrintheadGroundUS
'count the printheads shipped by ground to canada
For i = 2 To rowCount
If Cells(i, 4).Value = "CA" Then

If Cells(i, 7).Value = "Ground" Then

If Cells(i, 10).Value = "Print Head or Kit" Then

countPrintheadGroundCA = countPrintheadGroundCA + 1

End If

End If

End If

Next i
'Debug.Print countPrintheadGroundCA

'sum all printheads shipped via ground
sumPrintheadGround = countPrintheadGroundUS + countPrintheadGroundCA
'Debug.Print sumPrintheadGround
'count the OWW printheads
For i = 2 To rowCount
If Cells(i, 8).Value = "SP1K5167" Then

If Cells(i, 9).Value = "PRINTHEAD KIT, OWW" Then

countPrintheadOWW = countPrintheadOWW + 1

End If

End If

Next i
Debug.Print countPrintheadOWW
'count the color OOW printheads
'For i = 2 To rowCount
' If Cells(i, 8).Value = "SP1K5223" Then

' If Cells(i, 9).Value = "PRINTHEAD KIT, FOUR COLOR OOW" Then

' countPrintheadColorOOW = countPrintheadColorOOW + 1

' End If

' End If

'Next i
'Debug.Print countPrintheadColorOOW
'sum printheads by description
'sumPrintheadDesc = countPrintheadOWW + countPrintheadColorOOW
'Debug.Print sumPrintheadDesc
'count the requests for OWW printheads
'For i = 2 To rowCount
' If Cells(i, 8).Value = "SP1K5167" Then

' If Cells(i, 9).Value = "PRINTHEAD KIT, OWW" Then

' If Cells(i, 32).Value = 1 Then

' countPrintheadOWWrequest = countPrintheadOWWrequest + 1

' End If

' End If

' End If

'Next i
'Debug.Print countPrintheadOWWrequest
'count the requests for color OOW printheads
'For i = 2 To rowCount
' If Cells(i, 8).Value = "SP1K5223" Then

' If Cells(i, 9).Value = "PRINTHEAD KIT, FOUR COLOR OOW" Then

' If Cells(i, 32).Value = 1 Then

' countPrintheadColorOOWrequest = countPrintheadColorOOWrequest + 1

' End If

' End If

' End If

'Next i
'Debug.Print countPrintheadColorOOWrequest
'sum the printhead requests
'sumPrintheadDescRequest = countPrintheadOWWrequest + countPrintheadColorOOWrequest
'Debug.Print sumPrintheadDescRequest
End Sub

 

[steve the fish]

Hi. Why dont you store that in a lookup table in a worksheet? Its essentially a table you have hard coded which wouldnt be the way you would go normally.

 

[ehanley]

Using something like VLookup? The array of part numbers from the database can be any length, is it possible to use that type of formula with the array? Also, how do I store in a lookup table? Is that something with dictionaries?

 

[rlv01]

Vlookup is made for what you are trying to do. If as you say, you have saved the database to an excel workbook, and if you've structured the data such that the columns are fields and the rows are records (essentially a table). You should be able to apply VLookup with far less effort that your current approach. VLookup can be used directly as a cell formula or it can be used in VBA code (Application.VLookup)

https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

https://www.ablebits.com/office-add...cel-vlookup-tutorial-beginner/#vlookup-syntax


Also a good read

The XY Problem