PapaDos
12/18/2006 9:45:00 AM
I am not sure I understand fully, but this VBA code may put you on the right
track:
Sub insertNewLevels(rng_searched As Range, levels_to_search As Range)
Dim c, lvl
For Each lvl In levels_to_search.Columns(1)
Set c = rng_searched.Columns(1).Find(lvl.Value)
While Not c Is Nothing
c.Resize(1, 3).Copy c.Offset(0, 1)
c.Value = lvl.Offset(0, 1).Value
Set c = rng_searched.Columns(1).Find(lvl.Value)
Wend
Next lvl
End Sub
NOTES:
- The fisrt argument is the range of your first levels column (big).
- The second is a "column" range containing the name of the levels you want
to change. The code assumes that the new first level names are in a column
next to this range.
- THREE columns are copied to insert the new level. YOU MAY NEED TO CREATE A
NEW BLANK COLUMN (fourth level) BEFORE YOU RUN THIS CODE.
--
Regards,
Luc.
"Festina Lente"
"howardgrigg@gmail.com" wrote:
> Ok here is a challenge.
> Sheet with ~30,000 items each in a separate row with details including
> supplier number, description etc in each column. They are categorised
> into 3 levels eg, Consumables:Canon:Ink Cartrideges. Each level of
> description is in a separate column. On our website the first level are
> available to click on, however my problem is that I want to put some
> things into a different 1st level category. Like things that has a 1st
> level category of "systems software" and I want to change that to
> "Software" to cut down the number of 1st level categories. I could just
> do a find and replace on that column but I would like to move what is
> originally 1st level into 2nd level and what is 2nd level into 3rd
> level.
>
> Could we do a find then copy level 2 into level 3 then level 1 into
> level 2 then just do a replace on what is left in level 1. My issue is
> that I don't know how to implement this with a find and then loop
> plus multiple categories. If you need a better explanation please ask!
> Sorry it is a bit tricky to explain.
>
>