I found some problems with the first suggestion.. Try this instead:


Sub test()

Dim lCurNumber As Long
Dim lNewNumber As Long
Dim lRow1 As Long
Dim lRow2 As Long

With Application
.EnableEvents = False
.Calculation = xlCalculationManual
End With

lRow1 = 1
lRow2 = 1
While Range("A" & lRow1).Value < ""
lCurNumber = CLng(Mid(Range("A" & lRow1), 3))
lNewNumber = CLng(Mid(Range("C" & lRow2), 3))
While lCurNumber = lNewNumber
lRow2 = lRow2 + 1
With Range("C" & lRow2)
If .Text < "" Then
lNewNumber = CLng(Mid(.Value, 3))
End If
End With
If lRow2 = 65535 Then
MsgBox "No more Order Numbers!"
Exit Sub
End If
Wend
Range("B" & lRow1).Value = "WO" & Format(lNewNumber, "000000")
Range("C" & lRow2).Delete shift:=xlUp
lRow1 = lRow1 + 1
Wend

With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub


--
Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

Jim,

Give this a try:


Sub test()

Dim lCurNumber As Long
Dim lNewNumber As Long
Dim lRow1 As Long
Dim lRow2 As Long

With Application
.EnableEvents = False
.Calculation = xlCalculationManual
End With

lRow1 = 1
lRow2 = 1
While Range("A" & lRow1).Value < ""
lCurNumber = CLng(Mid(Range("A" & lRow1), 3))
lNewNumber = CLng(Mid(Range("C" & lRow1), 3))
While lCurNumber = lNewNumber
lRow2 = lRow2 + 1
With Range("C" & lRow2)
If .Text < "" Then
lNewNumber = CLng(Mid(.Value, 3))
End If
End With
If lRow2 = 65535 Then
MsgBox "No more Order Numbers!"
Exit Sub
End If
Wend
Range("B" & lRow1).Value = "WO" & Format(lNewNumber, "000000")
Range("C" & lRow2).Delete shift:=xlUp
lRow1 = lRow1 + 1
lRow2 = lRow2 + 1
Wend

With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub


--
Hope that helps.

Vergel Adriano


"Jim Berglund" wrote:

There are actually 25,000 numbers or so in Col C. I want to pick 'the next'
higher number from column C. For example,



Th efirst number in column A is WO000023. Look for a number in column C that
is larger - in this case, WO000024,and put it in Cell B1. Then delete the
value WO000024 from Column C and cycle through the rest of the numbers in
Column A.



Since we have just deleted a number from Column C, we should restart the
lookup at the next nember in Column C - not start from the beginning



Thanks,

Jim

"Mike H" wrote in message
...
Jim,

I'm struggling to understand the question. If we are to pick a number from
Col C and put it adjacent to a lower number in Col A then there aren't
enough
numbers in Col C as there are only 11 numbers that are higher than those
in
Col A. What is to methodology for creating more numbers?

Mike

"Jim Berglund" wrote:


I have two columns of Work Order Numbers, A & C

A B C
WO000023 WO000002
WO000027 WO000003
WO000029 WO000004
WO000084 WO000010
WO000086 WO000011
WO000099 WO000012
WO000101 WO000013
WO000115 WO000014
WO000153 WO000020
WO000164 WO000021
WO000165 WO000024
WO000175 WO000026
WO000176 WO000028
WO000185 WO000030
WO000189 WO000033
WO000190 WO000035
WO000192 WO000036
WO000199 WO000038
WO000203 WO000039
WO000209 WO000040
WO000214 WO000041
.... ...
Column A represents numbers that are already used; C consists of unused
numbers.
I want to create a new WO number for each value in A and place it (in
column
B) beside the corresponding number in A. It has to be a number from
column C
that is higher than the one in A. I then want to delete the value used
from
column C.

Any ideas on how to handle the logic?

Here's a start...
Dim i,j as Integer

for i=1 to 35000
'locate a number (eg. Cells(j,3) in column c that is greater than
Cells(i,1).value
???
Cells(i,2).value =Cells(j,3).value
Cells(j,3).value = ""


next
end

Any help in suggesting how to replace the ??? with code that will work,
will
be appreciated immensely.

Thanks,
Jim Berglund