Macro to populate a second column

[Bob]

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.

 

[Don Guillett]

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.

 

[Barb Reinhardt]

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?

 

[Mike H]

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

 

[NateBuckley]

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)

 

[Bob]

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

 

[Mike H]

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

 

[NateBuckley]

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!

 

[Mike H]

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 H]

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

 

[Bob]

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.