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
don't know which items one sheet 2 go on which rows on sheet 1.

I don't know how important column A (No) and B (Date) are in sheet1, but
ithere is no way it can be correlated to the data in sheet2. I think what
you really want is to take the data in sheet 2 and place it in sheet 1 with
highest operation number going first.

Think about what I wrote above and let me know how to proceeed.

"Cam" wrote:

Joel,

Thanks for your patience and help. The code is almost what I wanted.
When I run your data, it seemed like it look for data in sheet2 and add to
existence data in sheet1. I would like to update all the data in sheet1 by
using all data in sheet2.

Example:
Before running the macro, my data will have No., ShipDate & Model filled out
up to 50 rows. Then column D:K could be blank. Once the macro is ran, it will
fill in all the data starting from row 2 (No. 1001). Also, not fill in row 5
(No. 1004).
The S/O on sheet 2 will not shown up once all the operations are completed
and closed out. So if No. 1001 has all the S/O (not in sheet2 anymore) that
are completed I will hide 1001 row.
The same process repeat again.

No. ShipDate Model 1300 1300 1500 1500 Operation Status
1001 17-Aug 100
1002 18-Aug 200
1003 19-Aug 200
1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A
1006 21-Aug 200
1007 25-Aug 100
1008 25-Aug 100



"Joel" wrote:

You are going to have to fix the problems with your worksheet. Apparently
you have spaces the cells that contain numbers. Make sure all the numbers
are right justified in the cell (left justified indicates they are text).
You can easily get rid of the spaces by using the Spreadsheet Replace (under
edit menu). Highligh the cells containing number. the replace a single
space with nothing. Just enter a space in the from box and leave the replace
box empty.

The code becomes very complex to handle numbers and text mixed. fix both
sheet 1 and 2.

Below is the code I think you want. I put in the Operation number and well
as the SO number.


Sub checkso()

Const High2 = 0
Const High1 = 1
Const OP = 0
Const SO = 1
Dim R1300M100(2, 2)
Dim R1300M200(2, 2)
Dim R1500M100(2, 2)
Dim R1500M200(2, 2)

R1300M100(High1, OP) = 0
R1300M100(High2, OP) = 0
R1300M100(High1, SO) = 0
R1300M100(High2, SO) = 0
R1300M200(High1, OP) = 0
R1300M200(High2, OP) = 0
R1300M200(High1, SO) = 0
R1300M200(High2, SO) = 0
R1500M100(High1, OP) = 0
R1500M100(High2, OP) = 0
R1500M100(High1, SO) = 0
R1500M100(High2, SO) = 0
R1500M200(High1, OP) = 0
R1500M200(High2, OP) = 0
R1500M200(High1, SO) = 0
R1500M200(High2, SO) = 0


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

Sh1NewRow = LastRowSh1 + 1

With Sheets("Sheet2")

For Sh2RowCount = 2 To LastRowSh2

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

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

If Operation R1300M100(High1, OP) Then
R1300M100(High2, OP) = _
R1300M100(High1, OP)
R1300M100(High1, OP) = _
Operation
R1300M100(High2, SO) = _
R1300M100(High1, SO)
R1300M100(High1, SO) = _
ShippingOrder
Else
If Operation R1300M100(High2, OP) Then
R1300M100(High2, OP) = _
Operation
R1300M100(High2, SO) = _
ShippingOrder
End If
End If
End If
If .Cells(Sh2RowCount, "D").Value = 200 Then

If Operation R1300M200(High1, OP) Then
R1300M200(High2, OP) = _
R1300M200(High1, OP)
R1300M200(High1, OP) = _
Operation
R1300M200(High2, SO) = _
R1300M200(High1, SO)
R1300M200(High1, SO) = _
ShippingOrder
Else
If Operation R1300M200(High2, OP) Then
R1300M200(High2, OP) = _
Operation
R1300M200(High2, SO) = _
ShippingOrder
End If
End If
End If

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

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

If Operation R1500M100(High1, OP) Then
R1500M100(High2, OP) = _
R1500M100(High1, OP)
R1500M100(High1, OP) = _
Operation
R1500M100(High2, SO) = _
R1500M100(High1, SO)
R1500M100(High1, SO) = _
ShippingOrder
Else
If Operation R1500M100(High2, OP) Then
R1500M100(High2, OP) = _
Operation
R1500M100(High2, SO) = _
ShippingOrder
End If
End If
End If
If .Cells(Sh2RowCount, "D").Value = 200 Then

If Operation R1500M200(High1, OP) Then
R1500M200(High2, OP) = _
R1500M200(High1, OP)
R1500M200(High1, OP) = _
Operation
R1500M200(High2, SO) = _
R1500M200(High1, SO)
R1500M200(High1, SO) = _
ShippingOrder
Else
If Operation R1500M200(High2, OP) Then
R1500M200(High2, OP) = _
Operation
R1500M200(High2, SO) = _
ShippingOrder
End If
End If
End If
End If
Next Sh2RowCount
End With
With Sheets("Sheet1")

.Cells(Sh1NewRow, "C").Value = 100
.Cells(Sh1NewRow, "D").Value = _
R1300M100(High1, SO)
.Cells(Sh1NewRow, "E").Value = _
R1300M100(High2, SO)
.Cells(Sh1NewRow, "F").Value = _
R1500M100(High1, SO)
.Cells(Sh1NewRow, "G").Value = _
R1500M100(High2, SO)
.Cells(Sh1NewRow, "H").Value = _
R1300M100(High1, OP)
.Cells(Sh1NewRow, "I").Value = _
R1300M100(High2, OP)
.Cells(Sh1NewRow, "J").Value = _
R1500M100(High1, OP)
.Cells(Sh1NewRow, "K").Value = _
R1500M100(High2, OP)

Sh1NewRow = Sh1NewRow + 1

.Cells(Sh1NewRow, "C").Value = 200
.Cells(Sh1NewRow, "D").Value = _
R1300M200(High1, SO)
.Cells(Sh1NewRow, "E").Value = _
R1300M200(High2, SO)
.Cells(Sh1NewRow, "F").Value = _
R1500M200(High1, SO)
.Cells(Sh1NewRow, "G").Value = _
R1500M200(High2, SO)
.Cells(Sh1NewRow, "H").Value = _
R1300M200(High1, OP)
.Cells(Sh1NewRow, "I").Value = _
R1300M200(High2, OP)
.Cells(Sh1NewRow, "J").Value = _
R1500M200(High1, OP)
.Cells(Sh1NewRow, "K").Value = _
R1500M200(High2, OP)
End With
End Sub


"Cam" wrote:

Joel,

Thank for your response.
I'm still having problems. All the rows get the same data. I think you
want a new row, but didn't really say that. I don't have a date to start a
new row nor the 1001-1008 number.
I do want to start a new row, but the rows can be the same data depending if
there is any changes to the operation.
I'm not sure if there are two operations am I suppose to look for the two
highest operation numbers in sheet 2 and then add a new row?
Yes, for 1360 Ref column, pick the two highest operations and fill in the
data in sheet1, then fill in the next row with the next highest operation,
same for 1560.
What I can see it looks like its the columns H:K is what is changing every
day, not D:G. My code is changing D:G
Both D:G & H:K could be different every day where D:G changes once part# is
replaced or pulled out. Then reshuffle the fill in part#. H:K changes
depending on moving up the operation number.

Also, I ran the code and it kept with a mismatch type error. The model &
operation need to be changed to general or text instead of number. Thank
again.

"Joel" wrote:

Belwo is the code I have right now. Make sure there is no spaces in the
Model Number or Operation Number in sheet 1 or 2. I'm treating them as
number. they should be right justified in the cell with no double quotes in
front of the numbers. I can change the code if you are using text.

I'm still having problems. All the rows get the same data. I think you
want a new row, but didn't really say that. I don't have a date to start a
new row nor the 1001-1008 number.

I'm not sure if there are two operations am I suppose to look for the two
highest operation numbers in sheet 2 and then add a new row?

What I can see it looks like its the columns H:K is what is changing every
day, not D:G. My code is changing D:G

Sub checkso()

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

Sheets("Sheet1").Activate
With Sheets("Sheet2")
For Sh1RowCount = 2 To LastRowSh1
If Cells(Sh1RowCount, "D").Value < _
"vendor" Then