Looping statment
 

I have a spreadsheet that I use to track shipments and new orders on and I
thought it might be helpful to automat it a little.

In tab 1 I have last weeks orders.
In tab 2 I have this weeks orders.
A 3rd tab is present to list any differences that might occur from shipments
or new orders.
I have been working a code to compare list 1 to list 2 but can't seam to get
any results. I was wondering if this code will work or does someone have a
better idea.

Set MyFirstRange = Range("Prev_Cust_List") `(Tab 1~B3:B600)
Set MySecondRange = Range("Curr_Cust_List") `(Tab 2~B3:B600)
fnd = 0

For Each C In MyFirstRange
For Each n In MySecondRange
If C.Value = n.Value Then
Sheets("Changes to Make").Select
Range("A1").Select
C.Offset(0, 1).Value = n.Offset(0, 0).Value
fnd = 1
End If
Next

`With the customer list sort alph in coloum "A" of both tabs.

If any customer order #'s appear in on list but not the other then I want to
list them in a seperate tab to view sort. The layout of the preadsheet does
have multiple blank spaces seperating the different customers and each
customer could have multiple orders, which are listed by number (Starting at
2 cells down and 1 cell to the left.)

Any help would be greatly appreciated.
--
Pete

Looping statment
 

I'm confused about what's in column B and what's in column A.

But maybe something like this will give you an idea:

Option Explicit
Sub testme()

Dim oRow As Long
Dim ChWks As Worksheet
Dim res As Variant
Dim myCell As Range
Dim myFirstRange As Range
Dim mySecondRange As Range

Set ChWks = Worksheets("changes to make")
ChWks.Cells.Clear 'wipe out existing stuff.
ChWks.Range("a1").Resize(1, 2).Value = Array("Cust", "Warning")

Set myFirstRange = Range("Prev_Cust_List") '(Tab 1~B3:B600)
Set mySecondRange = Range("Curr_Cust_List") '(Tab 2~B3:B600)

oRow = 1
For Each myCell In myFirstRange.Cells
res = Application.Match(myCell.Value, mySecondRange, 0)
If IsError(res) Then
oRow = oRow + 1
ChWks.Cells(oRow, "A").Value = myCell.Value
ChWks.Cells(oRow, "B").Value = "Missing from Second Range"
End If
Next myCell

For Each myCell In mySecondRange.Cells
res = Application.Match(myCell.Value, myFirstRange, 0)
If IsError(res) Then
oRow = oRow + 1
ChWks.Cells(oRow, "A").Value = myCell.Value
ChWks.Cells(oRow, "B").Value = "Missing from first Range"
End If
Next myCell

End Sub

=====
If you want to look at more ideas, you may want to visit Chip Pearson's site:
http://www.cpearson.com/excel/duplicat.htm

He's got lots of stuff for working with this kind of data.


Pete wrote:

I have a spreadsheet that I use to track shipments and new orders on and I
thought it might be helpful to automat it a little.

In tab 1 I have last weeks orders.
In tab 2 I have this weeks orders.
A 3rd tab is present to list any differences that might occur from shipments
or new orders.
I have been working a code to compare list 1 to list 2 but can't seam to get
any results. I was wondering if this code will work or does someone have a
better idea.

Set MyFirstRange = Range("Prev_Cust_List") `(Tab 1~B3:B600)
Set MySecondRange = Range("Curr_Cust_List") `(Tab 2~B3:B600)
fnd = 0

For Each C In MyFirstRange
For Each n In MySecondRange
If C.Value = n.Value Then
Sheets("Changes to Make").Select
Range("A1").Select
C.Offset(0, 1).Value = n.Offset(0, 0).Value
fnd = 1
End If
Next

`With the customer list sort alph in coloum "A" of both tabs.

If any customer order #'s appear in on list but not the other then I want to
list them in a seperate tab to view sort. The layout of the preadsheet does
have multiple blank spaces seperating the different customers and each
customer could have multiple orders, which are listed by number (Starting at
2 cells down and 1 cell to the left.)

Any help would be greatly appreciated.
--
Pete

--

Dave Peterson