Code for IF
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
Cesar wrote:
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,
|