I have took a long winded approach but got it working as desired.

"winnie123" wrote:

Joel,

You are correct with your statement "How do we know which order is already
on the Open Sheet"

Could your code be amended so that when the order and line have been
found/identified then look in column D of the "shipped" sheet and use the row
with the highest number. Aternatively col E has the date the order line was
shipped so could we use the latest date ?

Thanks
Winnie

"Joel" wrote:

If there are duplicates how do we know which order is already on the Open
Order and which isn't? Here is the modified code. I used sumproduct to get
the count of duplicates


Sub LookupOrder()

Set bk = ThisWorkbook
Set Opensht = bk.Sheets("open")
Set Shipsht = bk.Sheets("shipped")

'work from last line to first line when inserting rows

With Opensht
Lrow = .Range("B" & Rows.Count).End(xlUp).Row
RowCount = Lrow
Do While RowCount = 2
OrderNum = .Range("C" & RowCount)
LineNum = .Range("D" & RowCount)

With Shipsht
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
Countformula = "sumproduct(" & _
"--(" & OrderNum & "=" & Shipsht.Name & "!B2:B" & LastRow & "),"
& _
"--(" & LineNum & "=" & Shipsht.Name & "!C2:C" & LastRow & "))"

Duplicates = Evaluate(Countformula)
If Duplicates 1 Then
Set C = .Columns("B").Find(what:=OrderNum, _
LookIn:=xlValues, _
lookat:=xlWhole)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
'check if line number also matches
If LineNum = C.Offset(0, 1) Then
'Add New Row
With Opensht
.Rows(RowCount + 1).Insert
'move column D
.Range("P" & (RowCount + 1)) = C.Offset(0, 2)
'move column E
.Range("O" & (RowCount + 1)) = C.Offset(0, 3)
'move column H
.Range("Q" & (RowCount + 1)) = C.Offset(0, 6)
'move column J
.Range("N" & (RowCount + 1)) = C.Offset(0, 8)
End With
End If
Set C = .Columns("B").FindNext(after:=C)
Loop While Not C Is Nothing And _
C.Address < FirstAddress

End If
End If
End With
RowCount = RowCount - 1


Loop

End With
End Sub




"winnie123" wrote:

Joel,

I have just run this code which took a good few minutes and what it seems to
have done is insert lines for all the shipped orders that marry up against
the order number and line number on the open sheet.

not sure why it was not doing that yesterday, but we will put that down to me

can we mod this so that it only inserts a line when there is more than one
instance of the order number and line number in the "shipped" sheet

Sorry for all the trouble I am causing but really appreciate your help

Thanks
Winnie

Sub LookupOrder()



Set bk = ThisWorkbook
Set Opensht = bk.Sheets("open")
Set ShipSht = bk.Sheets("shipped")

'work from last line to first line when inserting rows

With Opensht
Lrow = .Range("B" & Rows.Count).End(xlUp).Row
RowCount = Lrow
Do While RowCount = 2
OrderNum = .Range("C" & RowCount)
LineNum = .Range("D" & RowCount)

With ShipSht


Set C = .Columns("B").Find(what:=OrderNum, LookIn:=xlValues,
lookat:=xlWhole)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
'check if line number also matches
If LineNum = C.Offset(0, 1) Then
'Add New Row
With Opensht
.Rows(RowCount + 1).Insert
'move column D
.Range("P" & (RowCount + 1)) = C.Offset(0, 2)
'move column E
.Range("O" & (RowCount + 1)) = C.Offset(0, 3)
'move column H
.Range("Q" & (RowCount + 1)) = C.Offset(0, 6)
'move column J
.Range("N" & (RowCount + 1)) = C.Offset(0, 8)
End With
End If
Set C = .Columns("B").FindNext(after:=C)
Loop While Not C Is Nothing And C.Address < FirstAddress

End If

End With
RowCount = RowCount - 1


Loop

End With
End Sub



"Joel" wrote:

Is the code working or not working. Can't tell from the posting. The number
of rows should make a difference as long as the Order Numbers and Line Number
are the same. If the lastest version of the code so I can easily make
changes.

"winnie123" wrote:

Hi Joel,

I modified your code slighty

from

OrderNum = .Range("A" & RowCount)
LineNum = .Range("B" & RowCount)

To

OrderNum = .Range("C" & RowCount)
LineNum = .Range("D" & RowCount)

As the order number and line number on "open" sheet are in cols C and D

I then changed
With Opensht
.Rows(RowCount + 1).Insert
'move column D
.Range("N" & (RowCount + 1)) = _
c.Offset(0, 2)
'move column E
.Range("O" & (RowCount + 1)) = _
c.Offset(0, 3)
'move column H
.Range("P" & (RowCount + 1)) = _
c.Offset(0, 6)
'move column J
.Range("Q" & (RowCount + 1)) = _
c.Offset(0, 8)
End With
End If

To

With Opensht
.Rows(RowCount + 1).Insert
'move column D
.Range("P" & (RowCount + 1)) = _
c.Offset(0, 2)
'move column E
.Range("O" & (RowCount + 1)) = _
c.Offset(0, 3)
'move column H
.Range("Q" & (RowCount + 1)) = _
c.Offset(0, 6)
'move column J
.Range("N" & (RowCount + 1)) = _
c.Offset(0, 8)
End With
End If

Just so that the correct column in "shipped" married up to the correct
column in "open"

For info the number of lines on the "shipped" sheet is different to the
number of lines on the "open" sheet, maybe this is the reason?

Currently there are 341 rows on the "shipped" and only 155 on "open"
including headers.

Thanks
Winnie


"Joel" wrote:

You would only add the "exit Do" is you wanted to to return only the first
item found. I don't think you want to use it here. If the code isn't
working then some of the columns are not correct. Your description had the
Open sheet the order and line numbers in columns A & B respectively. The
shipped sheet had the same items in columns B & C. This may be the problem.

There are two types of problems you can have with this code.

1) No lines added to the Shipped sheet. This means the data isn't matching
between the two sheets.

2) Lines added to the shipped sheet but the wrong data is being put into the
Shipped sheets.


You may also have too many lines added to the shipped sheet and then we may
have to add additional filtering to the code. Lest get the code workig first
before we modify the algorithm.

"winnie123" wrote:

Hi Joel,

Thanks for getting back to me and the explanation on = sign.

I had noticed the typo with firstAddress

Unfotunately I still cant get it to work.

I have to go to work now so will try to work it out.

Just a thought I had a look back on some pervious posts and sometimes we used

Then
Found = True
Exit Do
End If

I will try to add this and see what I come up with.

Thanks Again
Winnie

"Joel" wrote:

You can move data into a cell either with copy and paste, or just using an
equal sign.

I found the mistakes. simple typo errors

1)
from
RowCount = RowCount = 1
to
RowCount = RowCount - 1

2)
from
Loop While Not c Is Nothing And _
c.Address < FirstAddress
to
Loop While Not c Is Nothing And _
c.Address < FirstAddr

"winnie123" wrote: