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.

--
Bob

right click the sheet tabview codeleft window worksheetright
windowchange
tie to a find & offset function or to a select case. Or possibly a formula
using match within index.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bob" wrote in message
...
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.

--
Bob

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?

--
HTH,
Barb Reinhardt



"Bob" wrote:

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.

--
Bob

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

"Bob" wrote:

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.

--
Bob

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" wrote:

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.

--
Bob

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


"Mike H" wrote:

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

"Bob" wrote:

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.

--
Bob

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" wrote:

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" wrote:

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.

--
Bob

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" wrote:

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" wrote:

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" wrote:

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.

--
Bob

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

"Bob" wrote:

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


"Mike H" wrote:

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

"Bob" wrote:

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.

--
Bob

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

"NateBuckley" wrote:

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" wrote:

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" wrote:

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" wrote:

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.

--
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.
--
Bob


"Mike H" wrote:

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

"Bob" wrote:

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


"Mike H" wrote:

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

"Bob" wrote:

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.

--
Bob