Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for IF
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, -- Cesar |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for IF
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
How can I modify my code to offset the defined range and repeat theprocedure instead of duplicating my code? | Excel Programming | |||
Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code does | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |