LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 18
Default Compating all rows in two sheets

I have two sheets with similar data

Sheet1
Name Date Type Amount
a 01-01-2010 x 100
b 01-01-2007 y 25
a 01-03-2008 x 124

Sheet2
Name Date Type Amount
a 01-01-2010 x 100
b 01-01-2007 y 20
a 01-03-2008 x 124
d 01-05-2007 x 100

I am using the below code to flag enties that are not exactly
matching. Thus it should flag for me that Name"b" and "d" are not
found in Sheet1. It works propoerly for "d" but not for "b". Obviously
I am amking a mistake. Please advise.

Sub compare()
'clear columne
Sheets("Sheet2").Range("E:E").ClearContents

'Find the last row to be evaluated for each sheet
Sheets("Sheet1").Select
lastrow1 = Range("A65536").End(xlUp).Row
Sheets("Sheet2").Select
lastrow2 = Range("A65536").End(xlUp).Row

Sheets("Sheet1").Select
For i = 2 To lastrow1
'Transfer each foled for a row into a variable
rec1 = Cells(i, 1).Value
rec2 = Cells(i, 2).Value
rec3 = Cells(i, 3).Value
rec4 = Cells(i, 4).Value


Sheets("Sheet2").Select
For j = 2 To lastrow2

'Look for a match
If Cells(i, 1).Value = rec1 And Cells(i, 2).Value = rec2 And _
Cells(i, 3).Value = rec3 And _
Cells(i, 4).Value = rec4 Then

Cells(i, 5).Value = "Found"

End If

Next j
Next i

End Sub
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
MACRO THAT CUT ROWS BETWEEN SHEETS AND ADD EMPTY ROWS udi Hen Excel Programming 0 December 18th 08 02:41 PM
Rows and Sheets VegasBurger Excel Worksheet Functions 1 October 31st 08 01:22 AM
"Add/Remove Rows Code" adds rows on grouped sheets, but won't remove rows. Conan Kelly Excel Programming 1 November 16th 07 10:41 PM
Matching rows in 2 sheets and copying matching rows from sheet 1 t fbagirov Excel Programming 1 April 8th 07 03:44 PM
"With Sheets" Issue - macro on one sheet to affect hidden rows on other sheets Punsterr Excel Programming 3 February 21st 06 04:01 AM


All times are GMT +1. The time now is 07:56 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"