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

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

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" <(E-Mail Removed)> wrote in message
news:F8380F90-3426-4344-9DEA-(E-Mail Removed)...
> 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
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:F8380F90-3426-4344-9DEA-(E-Mail Removed)...
> > 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
> >>
> >>
> >>
>
>
>

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" <(E-Mail Removed)> wrote in message
> > news:F8380F90-3426-4344-9DEA-(E-Mail Removed)...
> > > 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
> > >>
> > >>
> > >>
> >
> >
> >

I hadn't heard from anyone and took a more direct approach. Yours looks far
more elegant, but I don't understand a lot of it. (I hope to when I go
through it in detail.)

Here's what I ended up doing...

Sub HKY() 'Find higher WO Number
Dim i, j As Integer

j = 1
With ActiveSheet
For i = 1 To 25000
If .Cells(i, 1).Value = "" Then
GoTo Finish:
End If
While .Cells(1, 14).Value < .Cells(i, 1).Value
.Cells(1, 14).Select
Selection.Copy
.Cells(j, 16).Select
.Paste
.Cells(1, 14).Select
Selection.Delete Shift:=xlUp
j = j + 1
Wend
.Cells(i, 11).Value = .Cells(1, 14).Value
.Cells(1, 14).Select
Selection.Delete Shift:=xlUp
Next

Finish:
End With
End Sub

Thanks for the work you did on it.
Jim Berglund

"Vergel Adriano" <(E-Mail Removed)> wrote in message
news:8BCD70E4-096E-4990-9084-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
>> > news:F8380F90-3426-4344-9DEA-(E-Mail Removed)...
>> > > 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
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >