PDA

View Full Version : Using number of digits before "-" to determine whether number is type A or type B?



NateW
12-04-2007, 12:26 PM
Hi, Folks.

I'm looking for a way for a macro to look at a number, and tell from the number of digits before the character " - " whether the number is an order number or a requisition number.

The order number could look like this: 2576839-4.1
or like this: 2576839-4.112
or like this: 2576839-14.1
and so on, but in every instance of an order number, it would be 7 digits and then a " - ".

The requisition number could look like this: 321835-1
or like this: 321835-12
or like this: 321835-12.2
and so on, but in every instance of a req number, it would be 6 digits and then a " - ".

The digits after the " - " are still important, as they represent line numbers - so, in this sheet, there might be one reference to 321835, or there may be multiple, as in 321835-1, 321835-2, 321835-3.

The macro will need to go through a lengthy list of these numbers, in order to create a sorted list, with a blank row between unique numbers. Here's how the output could look:

321835-1
321835-2

321836-1

321837-3
321837-7
321837-14


Any ideas? As usual, any help is greatly appreciated.

Thanks!
Nate.

Bob Phillips
12-04-2007, 12:52 PM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow - 1 To 1 Step -1

If Left$(.Cells(i, TEST_COLUMN).Value, InStr(.Cells(i, TEST_COLUMN).Value, "-")) <> _
Left$(.Cells(i + 1, TEST_COLUMN).Value, InStr(.Cells(i + 1, TEST_COLUMN).Value, "-")) Then

.Rows(i + 1).Insert
End If
Next i
End With

End Sub

NateW
12-04-2007, 01:00 PM
Thanks...I think I can figure out what to do from here...

Cheers!