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

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

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