Comparison of two MS Excel Spreadsheets. 7

[ScottXJ]

Hi,

I am looking to compare the values of two MS Excel 2000 spreadsheets. I would like any exceptions to be exported to a separate spreadsheet. Therefore the basic logic breakdown would be as follows:

- Compare spreadsheet A and B
- if values are the same do nothing
- if values are different, export value to appropriate field in the Exceptions spreadsheet

How would I go about accomplishing this? Any assistance would be appreciated.

Thanks,

Scott.

 

[Crystalguru]

Yes, my data starts in row two. I use the text box to manually declare how many rows.

Both spreadsheets start with row 2. Good eye!

I copied the new code.

And TADA, same damn error. Same line.

When I step through it, when it steps to refsht.select, I can actually "see" the worksheet come up. Then I step to the next line: refArr = refSht.Range(Cells(2, 1), Cells(lRow, 1)) 'Loads Column A. I go to step through it and it errors 1004, again.

Do I have some option turned on? Why is this SO difficult.

Thank you so much for hanging in there with me!

 

[xlbo]

Ok - I think I'm gonna have to ask you to send me a copy of the workbook because I just can't grasp what is causing the error. I can run that exact code and it works fine. If there is any sensitive data, just replace it with fictional stuff.
Geoff dot Barraclough At Punchpubs.co.uk

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[xlbo]

For anyone who is interested, I believe the problem has been solved by replacing:
refArr = refSht.Range(Cells(2, 1), Cells(lRow, 1)) 'Loads Column A
with:
refArr = refSht.Range("A2:A" & lRow) 'Loads Column A

For some reason (unkown to me), the 2nd array loads perfectly well using the Cells syntax but the 1st one refuses to load unless the default Range syntax is used..........go figure ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Crystalguru]

Geoff,

Bizarre is a good word for it. The code works! Ya hoo.

One problem (you knew I would come up with this!), if I copy the Manufacturers name column to the Materials column (same exact names), it still comes up as red and No Match. hmmmm

Thanks again for taking the time for me. You are appreciated.

 

[xlbo]

Replied via email - typo in the code:
CompArr = compSht.Range("A2:AB" & lRow)
should've been
CompArr = compSht.Range("AB2:AB" & lRow)

Full code is:
Option Base 1
Sub GetDiffs()
Dim lRow As Long, cols As Integer, i As Integer, refArr As Variant, CompArr As Variant
Dim refSht As Worksheet, compSht As Worksheet, incr As Long
Set refSht = Sheets("Sheet1&quot
Set compSht = Sheets("Sheet2&quot
Application.ScreenUpdating = False

lRow = refSht.UsedRange.Rows.Count'This gets the last row with data in
refSht.Select
refArr = refSht.Range("A2:A" & lRow) 'Loads Column A
compSht.Select
CompArr = compSht.Range("AB2:AB" & lRow) 'Loads column AB on sheet2
For x = 1 To UBound(refArr)
If refArr(x, 1) <> CompArr(x, 1) Then
With compSht
.Cells(x, 49).Value = &quot;No Match&quot;
.Cells(x, 28).Interior.ColorIndex = 3
End With
Else
End If
Next
End Sub


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Crystalguru]

Geoff, (via email)

Typos are allowed! After what I have put you through..

If the list order is not exactly in the same order on both spreadsheets, will this give me an error? I tested this, putting in two rows exactly the same then changed one of the values - it errors.

If there is a No Match error message, can I retain the error messages from the first sub routine? For example: Material Name is longer than 80 characters, No match.

Or am I asking for to much?

I am definately learning from this experience!

Thanks

 

[Crystalguru]

I am signing off this LONG post. The code Geoff provided me meets the requirements I had requested.

I am starting a new post - &quot;code tweaking&quot; to meet some of my other needs with this particular post. Feel free to join me there!

crystalguru