Code for IF

  • Thread starter Thread starter Cesar
  • Start date Start date
C

Cesar

Hello All,
I have a range of cells (A1:A3000) with codes for items in a warehouse,
there are about 3000 items, there are also 10 cathegories. I want to, in
range B1:B3000 write the name of the cathegory that the item belong; the
items' codes are totaly different one from the other, example:

A B
ACTU VALVES
EFTG CONDUIT
COND CONDUIT
IFTG TUBING
XMTR TRANSMITTER

I have done this using a lot nested IF on cells B:B3000.
Is there a way to write a code to do this faster?

Thanks in Advance,
 
Hi Cesar

I would suggest you write a vlookup to check the unique code against
each category. In another table create a list of all the unique
items. Then next to the item place its category name.

Then in cell B2 type this formula

=Vlookup(A1,MyTable,2,0)

Where Mytable is reference to the list of items and categories and 2
is the columns offset from the found item.

My concern with this suggestion is if you know how to do this with a
nested if statement then a vlookup should have occurred to you.

Take care

Marcus
 
I would make a data sheet and make a table of items and category there.
A table looks like below, for example.

A B C D E
1 Category VALVES CONDUIT TUBING TRANSMITTER
2 item ACTU EFTG IFTG XMTR
COND

Try this one. I named data sheet as "Category" in the code below.

Sub Categorytest()
Dim TarRng As Range
Dim startrow As Long, strowdata As Long, codecolnum As Long
Dim codecol As String

With Worksheets("Category")

strowdata = 1 'start row's number in Category sheet
codecolnum = 2 'start column's number in Category sheet
startrow = 2 'start row's number in data sheet
codecol = "A" 'column where code reside

Set TarRng = .Cells(strowdata, "A").CurrentRegion
Set TarRng = TarRng.Offset(, codecolnum - 1) _
.Resize(, TarRng.Columns.Count - 1)

For i = startrow To Cells(startrow, codecol).End(xlDown).Row
Set tmp = TarRng.Find(Cells(i, codecol).Value, lookat:=xlWhole)

If Not tmp Is Nothing Then
Cells(i, codecol).Offset(0, 1) = .Cells(strowdata, tmp.Column)
Else
Cells(i, codecol).Offset(0, 1) = "Can't find Category"
End If
Next

End With

End Sub

Keiji
 
Back
Top