Joel,

Thank you again, I ran some test data and it looked like it's working, but I
do need couple changes to the code.

1) The Model number is in column "O" on sheet3
2) After the code is ran, the corresponding operation number is in column B:
G, but they need to be in column O:T (right after the 6 columns for Item) on
sheet1 & 2.
Also, need to add:
1) on sheet3 where column H is the delivery date, so if I have more than one
orders that have are in the same model, item and operation, I would like to
also sort (ascending) by delivery date before transfering the data to sheet1
or sheet2.
2) If later on (expecting) I have more model (like 300) to add (extra
sheet), is it easy to change the code to add them in?

Again, thank you.

"Joel" wrote:

I did the best I could with the data you had posted. i couldn't tell what
columns the data was in. Also you were missing the Model number in sheet 3.
I put the Model in Column B of sheet 3.

Const OP = 0
Const SO = 1

Const Ref1300 = 0
Const Ref1500 = 1
Const REf1700 = 2

Sub checkso()

Dim R1300M100(1000, 2)
Dim R1300M200(1000, 2)
Dim R1500M100(1000, 2)
Dim R1500M200(1000, 2)
Dim R1700M100(1000, 2)
Dim R1700M200(1000, 2)

With Sheets("Sheet1")
LastRowSh1 = _
.Cells(Rows.Count, "H").End(xlUp).Row
If LastRowSh1 < 1 Then
Set ColHRange = .Range(.Cells(2, "H"), _
.Cells(LastRowSh1, "H"))
For Each cell In ColHRange
If cell < "vendor" Then
Rows(cell.Row).ClearContents
End If
Next cell
End If
End With
With Sheets("Sheet2")
LastRowSh2 = _
.Cells(Rows.Count, "H").End(xlUp).Row
If LastRowSh2 < 1 Then
Set ColHRange = .Range(.Cells(2, "H"), _
.Cells(LastRowSh2, "H"))
For Each cell In ColHRange
If cell < "vendor" Then
Rows(cell.Row).ClearContents
End If
Next cell
End If
End With
LastRowSh3 = Sheets("Sheet3"). _
Cells(Rows.Count, "A").End(xlUp).Row

R1300M100Count = 0
R1300M200Count = 0
R1500M100Count = 0
R1500M200Count = 0
R1700M100Count = 0
R1700M200Count = 0

With Sheets("Sheet3")

For Sh3RowCount = 2 To LastRowSh3

OPeration = _
.Cells(Sh3RowCount, "L").Value
ShippingOrder = _
.Cells(Sh3RowCount, "A").Value
Model = _
.Cells(Sh3RowCount, "B").Value
ITEM = Trim(.Cells(Sh3RowCount, "C"))
If Left(ITEM, 2) = "13" Then

If Model = 100 Then

R1300M100Count = R1300M100Count + 1
R1300M100(R1300M100Count, OP) = _
OPeration
R1300M100(R1300M100Count, SO) = _
ShippingOrder
End If
If Model = 200 Then

R1300M200Count = R1300M200Count + 1
R1300M200(R1300M200Count, OP) = _
OPeration
R1300M200(R1300M200Count, SO) = _
ShippingOrder
End If

End If
If Left(ITEM, 2) = "15" Then

If Model = 100 Then

R1500M100Count = R1500M100Count + 1
R1500M100(R1500M100Count, OP) = _
OPeration
R1500M100(R1500M100Count, SO) = _
ShippingOrder
End If
If Model = 200 Then

R1500M200Count = R1500M200Count + 1
R1500M200(R1500M200Count, OP) = _
OPeration
R1500M200(R1500M200Count, SO) = _
ShippingOrder
End If
End If
If Left(ITEM, 2) = "17" Then

If Model = 100 Then

R1700M100Count = R1700M100Count + 1
R1700M100(R1700M100Count, OP) = _
OPeration
R1700M100(R1700M100Count, SO) = _
ShippingOrder
End If
If Model = 200 Then

R1700M200Count = R1700M200Count + 1
R1700M200(R1700M200Count, OP) = _
OPeration
R1700M200(R1700M200Count, SO) = _
ShippingOrder
End If
End If
Next Sh3RowCount
End With

Call SortData(R1300M100, R1300M100Count)
Call SortData(R1300M200, R1300M200Count)
Call SortData(R1500M100, R1500M100Count)
Call SortData(R1500M200, R1500M200Count)
Call SortData(R1700M100, R1700M100Count)
Call SortData(R1700M200, R1700M200Count)

Call InsertData(R1300M100, R1300M100Count, _
Ref1300, 100, "Sheet1")
Call InsertData(R1300M200, R1300M200Count, _
Ref1300, 200, "Sheet2")
Call InsertData(R1500M100, R1500M100Count, _
Ref1500, 100, "Sheet1")
Call InsertData(R1500M200, R1500M200Count, _
Ref1500, 200, "Sheet2")
Call InsertData(R1700M100, R1700M100Count, _
REf1700, 100, "Sheet1")
Call InsertData(R1700M200, R1700M200Count, _
REf1700, 200, "Sheet2")

End Sub

Sub SortData(ByRef MyArray() As Variant, Count)

For i = 0 To (Count - 1)
For j = (i + 1) To Count
If MyArray(j, OP) MyArray(i, OP) Then
Temp = MyArray(i, OP)
MyArray(i, OP) = MyArray(j, OP)
MyArray(j, OP) = Temp

Temp = MyArray(i, SO)
MyArray(i, SO) = MyArray(j, SO)
MyArray(j, SO) = Temp
End If
Next j
Next i
End Sub
Sub InsertData(ByRef MyArray() As Variant, _
Count, Ref, Model, InsertSheet)

With Sheets(InsertSheet)
RowCount = 2
MyOffset = 0
Do While (Not IsEmpty(.Cells(RowCount, "B"). _
Offset(0, 2 * Ref))) And _
((.Cells(RowCount, "H") < Model) Or _
(.Cells(RowCount, "I") = "vendor"))

RowCount = RowCount + 1
Loop
For LoopCount = 0 To (Count - 1)
.Cells(RowCount, "I"). _
Offset(0, (2 * Ref) + MyOffset) = _
MyArray(LoopCount, SO)
.Cells(RowCount, "B"). _
Offset(0, (2 * Ref) + MyOffset) = _
MyArray(LoopCount, OP)

If MyOffset = 0 Then
.Cells(RowCount, "H").Value = Model
MyOffset = 1
Else
RowCount = RowCount + 1
Do While (Not IsEmpty( _
.Cells(RowCount, "B"). _
Offset(0, 2 * Ref))) And _
((.Cells(RowCount, "H") < Model) Or _
(.Cells(RowCount, "I") = "vendor"))

RowCount = RowCount + 1
Loop
MyOffset = 0
End If
Next LoopCount
End With
End Sub

"Joel" wrote:

I will try to get to it tonight. From you descrion the problem has to be in
this do loop

Do While (.Cells(RowCount, "D"). _
Offset(0, Ref) < "") And _
((.Cells(RowCount, "C") < Model) Or _
(.Cells(RowCount, "D") = "vendor"))

I don't plan to change this loop, but will try to as some debug statement to
help find the problem. Because two line of data get output it means the sub
InsertDat is failing which is the last thing run in the macro. The Do
statementt is the only place in this sub that will cause a failure.. If a
mismatch is occuring it means a cell is empty or the code is comparing a
number with a string.

Try making this change and let me know what happens

Do While (not isempty(.Cells(RowCount, "D"). _
Offset(0, Ref))) And _
((.Cells(RowCount, "C") < Model) Or _
(.Cells(RowCount, "D") = "vendor"))


"Cam" wrote:

Joel,

The original code has mismatch error cause the model column is a formula, I
guess. The second time, it only filled in two rows out of all the raw data in
sheet2.
Can we start fresh? Another set requirement of 1760 was added. Also, the
column changed. The letter represent column and because the model are split
into two sheets, I hope the code is simplier. Here's the new data I would
like to perform on the VBA. I hope this example is a lot more clearer.

sheet1
LINE B C D E F G MODEL 1360-100 1560-100 1760-100
1002 100
1004 100
1005 100
1006 100
1010 100

sheet2
LINE B C D E F G MODEL 1360-100 1560-100 1760-100
1003 200
1007 200
1008 200
1009 200

sheet3
Order B Item D E F G H I J K
Oper. M N
220720 1360-101 90
220721 1360-101 40
205659 1360-101 30
205660 1560-101 40
224256 1760-101 50
224253 1760-101 10
224251 1360-100 100
224252 1360-100 90
225157 1360-100 50
225158 1560-100 40
225159 1560-100 30
214058 1760-100 80
214063 1760-100 70
218799 1760-100 70
218800 1760-100 50

RESULTS:
sheet1
LINE B C D E F G MODEL 1360-100 1560-100 1760-100
1002 100 100 90 40 30 80 70
1004 100 50 70 50
1005 100
1006 100
1010 100

sheet2
LINE B C D E F G MODEL 1360-100 1560-100 1760-100
1003 200 90 40 40 50 10
1007 200 30
1008 200
1009 200



"Joel" wrote:

Did the code every work? You have a new problem now which is more
complicated and before we start changing code lets get the easier first
problem solved. Once I have the first problem solve the changes are pretty