Macro to populate a second column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 22 Mar 2008 13:47 GMT

I have a part list which I update monthly. In most cases the part numbers
are 10+ characters long however after the first 3 or 4 characters I can
determine what kind of part it is. What I would like to do is create a macro
that based on the first 3 to 4 characters would populate the product category
field in the second column. For example part number DF24-10W52-08LPHHN in
column "A" would equal product Category in Column "B". I would just use DF24
in the macro. Also my part list is about 40,000 lines.

Thanks.

Signature

Bob

Reply to this Message

Don Guillett - 22 Mar 2008 13:58 GMT

right click the sheet tab>view code>left window worksheet>right
window>change
tie to a find & offset function or to a select case. Or possibly a formula
using match within index.

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I have a part list which I update monthly. In most cases the part numbers
> are 10+ characters long however after the first 3 or 4 characters I can
[quoted text clipped - 8 lines]
>
> Thanks.

Reply to this Message

Mike H - 22 Mar 2008 14:00 GMT

Bob,

Reading the 4 leftmost characters from column A used range is simple enough
with the code below for example but you don't tell us how to populate column
B. Where are the data to do this? You may not even need a macro.

Sub sonic()
Dim MyRange As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
partno = Left(c.Value, 4)
MsgBox partno
Next
End Sub

Mike

> I have a part list which I update monthly. In most cases the part numbers
> are 10+ characters long however after the first 3 or 4 characters I can
[quoted text clipped - 5 lines]
>
> Thanks.

Reply to this Message

Bob - 22 Mar 2008 14:08 GMT

Mike,
My product categories would be in a separate sheet. There are only 10
categories.

Signature

Bob

> Bob,
>
[quoted text clipped - 23 lines]
> >
> > Thanks.

Reply to this Message

Mike H - 22 Mar 2008 14:19 GMT

Bob,

You probably don't need a macro. I still don't understand how your data are
laid out but you could build a table like this which in my case is in A1 - B10

DF24    Spanners
DF25    Nuts
DF26    Bolts
DF27    Washers
DF28    Pins
DF29    Clips
DF30    Dogs
DF31    Cats
DF32    Mice
DF33    Widgets

Then on your other sheet in b1 the formula
=VLOOKUP(LEFT(A1,4),Sheet2!$A$1:$B$10,2,FALSE)

Would check the first 4 characters of A1 against the table and return the
value of column B. Double click the fill handle to fill down to the length of
Column A

> Mike,
> My product categories would be in a separate sheet. There are only 10
[quoted text clipped - 27 lines]
> > >
> > > Thanks.

Reply to this Message

Bob - 22 Mar 2008 14:26 GMT

Thanks Mike. I'll try the Vlookup instead. Basically right now my table is
only a two ccolumn table such as the one you have below. However , when I
update my list monthly there will be some new parts in the table which need
to be categorized. I thought maybe a macro would be able to do this but I
think the vlookup might be just as good.

Signature

Bob

> Bob,
>
[quoted text clipped - 50 lines]
> > > >
> > > > Thanks.

Reply to this Message

Barb Reinhardt - 22 Mar 2008 14:00 GMT

You can do this with a formula

=left(A1,search("-",a1)-1))

For your sample part number (DF24-10W52-08LPHHN), you want to parse out
DF24. What do you want to do after that?

Signature

HTH,
Barb Reinhardt

> I have a part list which I update monthly. In most cases the part numbers
> are 10+ characters long however after the first 3 or 4 characters I can
[quoted text clipped - 5 lines]
>
> Thanks.

Reply to this Message

NateBuckley - 22 Mar 2008 14:01 GMT

You could try this

Sub populateSecondCol()

dim amountOfParts as Long
amountOfParts = Sheets("SheetName").Range("A65535").End(xlUp).Row
for i = 1 to amountOfParts
Sheets("SheetName").Cells(i, 2).Value = Mid(Sheets("SheetName").Cells(i,
1).Value, 1, 4)
next i
End sub

I'm thinking there will be a better way to do this, especially because there
are so many parts to check, possibly some special excel functions to use. But
this does the trick if no-one else offers a better solution (which I'm sure
they will)

> I have a part list which I update monthly. In most cases the part numbers
> are 10+ characters long however after the first 3 or 4 characters I can
[quoted text clipped - 5 lines]
>
> Thanks.

Reply to this Message

Mike H - 22 Mar 2008 14:08 GMT

Nate,

With the introduction of Excel 2007 with it's plethora of rows (1 million I
understand)I would suggest you modify the method you are using to get lastrow
to

amountOfParts = Sheets("SheetName").Cells(Rows.Count, "A").End(xlUp).Row

Mike

> You could try this
>
[quoted text clipped - 22 lines]
> >
> > Thanks.

Reply to this Message

NateBuckley - 22 Mar 2008 14:16 GMT

Mike,

Appreciated, although I don't use Excel 2007 at the moment, That's a very
good suggestion. It'll save me the constantly headache of accidently putting
655336 instead of 65536, something I continue to do for some reason. I can
just skip that number altogether.

Cheers!

> Nate,
>
[quoted text clipped - 32 lines]
> > >
> > > Thanks.

Reply to this Message

Mike H - 22 Mar 2008 14:20 GMT

Nate,

I don't use 2007 either but a couple of assists I've tried for others have
failed because 65536 does just that and misses the extra rows.

Mike

> Mike,
>
[quoted text clipped - 41 lines]
> > > >
> > > > Thanks.

Reply to this Message