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
easy to get the 2nd version working.

Why wasn't the original code working for you. Explain in detail!

"Cam" wrote:

Joel,

The code is not working for me. It just filled out blank data in sheet1.
I thought maybe to make it simplier by seperating the two models into
individual sheet.

So, sheet3 will still be the raw data, but now sheet1 is for 100 model and
sheet2 for 200 model. look something like below. All the code have to do is
to look at the raw data in sheet3, sort by model number and operation. Then,
for all the data for model 100 in sheet1, fill in the S/O starting column D:J
with the S/O that have the highest operation go first. Same for model 200. I
forgot to mention that the Ref column in raw data sheet has difference number
for model 100 & 200. See sample below.

sheet1
No. ShipDate Model 1301 1301 1501 1501 Operation
1001 17-Aug 100
1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A
1007 25-Aug 100
1008 25-Aug 100

sheet2
No. ShipDate Model 1302 1302 1502 1502 Operation
1002 18-Aug 200
1003 19-Aug 200
1006 21-Aug 200

Sorry, for all the confusion.


"Joel" wrote:

Change one line of code to clear cell C to Z (make wnhat ever you need)

From: clear entire row
Rows(cell.Row).ClearContents

To: clear cells C to Z
.Range("C" & cell.Row & ":Z" & _
cell.Row).ClearContents

"Cam" wrote:

Thank you Joel.

Can I not clear column A & B on sheet1? They will be template and
information is fixed (all filled out up to No. 1100, etc).

"Joel" wrote:

I ws expecting that you would agree with me. Have been working the code
before you asked for it. The code will clear sheet 1 and skip and rows with
vendor in column D. I also cleared column A and B because I don't know what
should be in these columns. the code has 3 subroutines to make the code
common for all models and Ref values. Run checkso()


Const OP = 0
Const SO = 1
Const Ref1300 = 0
Const Ref1500 = 2

Sub checkso()

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

With Sheets("Sheet1")
LastRowSh1 = _
.Cells(Rows.Count, "C").End(xlUp).Row
Set ColDRange = .Range(.Cells(2, "D"), _
.Cells(LastRowSh1, "D"))
For Each cell In ColDRange
If cell < "vendor" Then
Rows(cell.Row).ClearContents
End If
Next cell
End With

LastRowSh2 = Sheets("Sheet2"). _
Cells(Rows.Count, "A").End(xlUp).Row

R1300M100Count = 0
R1300M200Count = 0
R1500M100Count = 0
R1500M200Count = 0

With Sheets("Sheet2")

For Sh2RowCount = 2 To LastRowSh2

OPeration = .Cells(Sh2RowCount, "B")
ShippingOrder = .Cells(Sh2RowCount, "A")
Model = .Cells(Sh2RowCount, "D")
If .Cells(Sh2RowCount, "C").Value = 1300 Then

If .Cells(Sh2RowCount, "D").Value = 100 Then

R1300M100Count = R1300M100Count + 1
R1300M100(R1300M100Count, OP) = _
OPeration
R1300M100(R1300M100Count, SO) = _
ShippingOrder
End If
If .Cells(Sh2RowCount, "D").Value = 200 Then

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

End If
If .Cells(Sh2RowCount, "C").Value = 1500 Then

If .Cells(Sh2RowCount, "D").Value = 100 Then

R1500M100Count = R1500M100Count + 1
R1500M100(R1500M100Count, OP) = _
OPeration
R1500M100(R1500M100Count, SO) = _
ShippingOrder
End If
If .Cells(Sh2RowCount, "D").Value = 200 Then

R1500M200Count = R1500M200Count + 1
R1500M200(R1500M200Count, OP) = _
OPeration
R1500M200(R1500M200Count, SO) = _
ShippingOrder
End If
End If
Next Sh2RowCount
End With
Call SortData(R1300M100, R1300M100Count)
Call SortData(R1300M200, R1300M200Count)
Call SortData(R1500M100, R1500M100Count)
Call SortData(R1500M200, R1500M200Count)

Call InsertData(R1300M100, R1300M100Count, _
Ref1300, 100)
Call InsertData(R1300M200, R1300M200Count, _
Ref1300, 200)
Call InsertData(R1500M100, R1500M100Count, _
Ref1500, 100)
Call InsertData(R1500M200, R1500M200Count, _
Ref1500, 200)

End Sub

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

For i = 0 To (Count - 2)
For j = (i + 1) To (Count - 1)
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)

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

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

If MyOffset = 0 Then
.Cells(RowCount, "C").Value = Model
MyOffset = 1
Else
RowCount = RowCount + 1
Do While (.Cells(RowCount, "D"). _
Offset(0, Ref) < "") And _
((.Cells(RowCount, "C") < Model) Or _
(.Cells(RowCount, "D") = "vendor"))

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


"Cam" wrote:

Joel,

Thank you for leading me to some thought. I think you are right that all I
wanted was to take the raw data in sheet2 pulled from our system then place
S/O in sheet2 based on the model and highest operation number going first.

So, what I need to include in the code is the refresh (delete) all existing
data in sheet1 first. Also, what I need to do manually is also, hide row with
the No. (etc.. 1001) that are already completed so it will always start
filling in from the first row and down. And I am not sure if the code also
need to say not to fill in the data with the rows hidden.

"Joel" wrote:

Cam: It looks like you want my 1st solution, not the second solution! The
problem is there are multiple rows on Sheet 1 with the same model number. I