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

Model = Cells(Sh1RowCount, "C").Value
For Refcol = 4 To 6 Step 2
Ref = Cells(1, Refcol).Value
ItemOffset = 0

Found = False
For Sh2RowCount = _
2 To LastRowSh2

If (.Cells(Sh2RowCount, "C").Value = _
Ref) And _
(.Cells(Sh2RowCount, "D").Value = _
Model) Then


If ItemOffset = 0 Then
Cells(Sh1RowCount, Refcol) = _
.Cells(Sh2RowCount, "A").Value
ItemOffset = 1

Else
Cells(Sh1RowCount, Refcol + 1) = _
.Cells(Sh2RowCount, "A").Value
Found = True
Exit For
End If
End If
Next Sh2RowCount

If Found = False Then
If ItemOffset = 0 Then
Cells(Sh1RowCount, Refcol) = ""
Cells(Sh1RowCount, Refcol + 1) = ""
Else
Cells(Sh1RowCount, Refcol + 1) = ""
End If
End If
Next Refcol
End If
Next Sh1RowCount
End With
End Sub


"Cam" wrote:

Hello Joel,

I will try to explain as clear as possible.
1) Where does the date come from?
The date came from a raw data from sheet2 so every S/O has a date field.

2) Is it the date when the entry is made?
It is not the date where the entry is made.

3) Is your Raw data really a group of data from several days of entries? If
so can you split the raw data by dates?
The raw data is pulled from our system so everyday the S/O can be obtained
and will have higher operation number depending whether there are work done
on that S/O, when all the operations are completed, it will have a "closed"
status.

4) Does it make a diference if the raw data goes into the first or 2nd
column for each model? Is it acceptable for the 1st raw data to go into the
left column and the 2nd in the right column?
No, it does not make any different if which 1st or 2nd column of each model.
Yes, it is acceptable.

I hope this explain what I am trying to do manually and hopefully can
translate into macro automated.
The No. column from sheet1 is the next product due for the customer. There
are two difference models (100 or 200)for each No. There are 4 parts (1300
(2) & 1500 (2)) to complete each No. The raw data have S/O that are tied to
1300 or 1500 ref.

Each S/O goes through 15 operations before it is completed. Everyday, the
operation status of the S/O changes depending on what operation it is at. The
important thing is that we want to make sure that we are working on the S/O
with the right model for the next No.
From the data below, the next No. would be 1002 which is model 200 (we skip
No. 1001 because it is outsource to a vendor). The raw data would have S/O
for model 200 at different operation status. The macro would than find the
right S/O based on the model 200 and latest operation, then fill in 4 columns
of each No. Everyday the operation for each S/O is updated once I pulled the
raw data in sheet2.

Thank for your help.


"Joel" wrote:

I would of responded right after your posted your request, but I was
confused. After a long time thinking about what you want I need more details.

I've been trying to determine if you are adding new rows to sheet 1, or
filling in data like you requested stated.

It seems to make more sense that you are adding new rows on a daily bases.
The question I have about this method are as follows:
1) Where does the date come from?
2) Is it the date when the entry is made?
3) Is your Raw data really a group of data from several days of entries? If
so can you split the raw data by dates?
4) Does it make a diference if the raw data goes into the first or 2nd
column for each model? Is it acceptable for the 1st raw data to go into the
left column and the 2nd in the right column?

If you are filling in the table I'm have different questions:
1) Can you give an exanmple of the empty table before the raw data is
entered.
2) How do you know which entry is for each date?
3) Does it make a diference if the raw data goes into the first or 2nd
column for each model?

"Cam" wrote:

Hello,

I have two different data in two worksheets, worksheet 1 is the main data
that I would like to create a macro for. Sheet2 contains raw data that I pull
daily or weekly.

What I would like to achieve for the VBA macro is to dump the raw data in
sheet2 into sheet1, but put into the right cell. The macro needs to be able
to:
1) Fill in the s/o number to column D:G based on the latest operation number
for each model & part# type column.
2) Each model in the row will always have 2 (1300) & 2 (1500)
3) The row with "vendor" fill in do not require s/o fill in.
4) Column H:K, I can do a Vlookup to fill in the operation number from the
raw data.

Example: for row with No. 1002 & 1003 that have the same model, 218029
(1300) @ operation 100 is a later operation than 215789 (1300) @ operation
90, so the first s/o to fill in row with 1002 is 218031 and so on.

Here is the data sample:

Sheet1
No. ShipDate Model 1300 1300 1500 1500 Operation
Status
1001 17-Aug 100 vendor vendor vendor vendor #N/A #N/A #N/A #N/A
1002 18-Aug 200 218029 218031 215226 214526 100 95 320 197
1003 19-Aug 200 215789 218033 216999 216998 90 90 320
162
1004 19-Aug 100 215048 215780 215778 217693 90 90 410
180
1005 20-Aug 100 vendor vendor vendor vendor #N/A #N/A #N/A #N/A
1006 21-Aug 200 218035 216994 217000 218041 90 80 162
165
1007 25-Aug 100 216966 218975 218042 217001 80 70 300
150
1008 25-Aug 100 215765 215769 215779 215785 50 60 250
100

Sheet2
Raw Data
S/O Operation Ref Model
218029 100 1300 200
215789 90 1300 200
215048 90 1300 100
218035 90 1300 200
216966 80 1300 100
215765 50 1300 100
218974 20 1300 200
218031 95 1300 200
218033 90 1300 200
215780 90 1300 100
216994 80 1300 200
218975 180 1300 100
215769 320 1300 100
219680 150 1300 200
215226 320 1500 200
216999 320 1500 200
215778 410 1500 100
217000 162 1500 200
218042 150 1500 100
215779 250 1500 100
215790 110 1500 200
214526 197 1500 200
216998 162 1500 200
217693 180 1500 100
218041 165 1500 200
217001 150 1500 100
215785 10 1500 100
218039 110 1500 200