How to compare two sheets

[anyname]

Hi All,

I need to select matching rows between two sheets. I have tried the following VBA code but it does not work.

it should get the first row from sheet1
check all rows in sheet two

And then work down all the rows in sheet1 against sheet2


[vba]Dim varAAcode As String 'items to check from sheet1
Dim varHU As String 'items to check from sheet1
Dim varBatch As String 'items to check from sheet1
Dim varQuant As String 'items to check from sheet1
Dim varAAcodeToMatch As String 'items to check from sheet2
Dim varHUToMatch As String 'items to check from sheet2
Dim varBatchToMatch As String 'items to check from sheet2
Dim varQuantToMatch As String 'items to check from sheet2
Dim varSheet1Position As Integer 'Counter in sheet1
Dim varSheet2Position As Integer 'Counter in sheet2
Dim varSheet1Count As Integer 'Total rows on sheet1
Dim varSheet2Count As Integer 'Total rows on sheet2
Dim varSheet1MatchRow As String 'Address of match in Sheet1
Dim varSheet2MatchRow As String 'Address of match in Sheet2

Sub Start()
varSheet1Count = Application.CountA(Range("A:A")) 'Total count of rows in sheet1
varSheet2Count = Application.CountA(Sheet2.Range("A:A")) 'Total count of rows in sheet2
varSheet1Position = 1 'Start position for sheet1
varSheet2Position = 1 'Start position for sheet2
Call SheetPositionForSheet1
Call SheetPositionForSheet2
End Sub

Public Sub SheetPositionForSheet1() 'Get var's for sheet1
ActiveCell.Cells(varSheet1Position, 1).Select
varAAcode = ActiveCell.Value
varHU = ActiveCell.Offset(0, 1).Value
varBatch = ActiveCell.Offset(0, 2).Value
varQuant = ActiveCell.Offset(0, 3).Value
End Sub

Public Sub SheetPositionForSheet2() 'Get var's for sheet2
Application.GoTo Worksheets("Sheet2").Range("A1"), True
ActiveCell.Cells(varSheet2Position, 1).Select
varAAcodeToMatch = ActiveCell.Value
varHUToMatch = ActiveCell.Offset(0, 1).Value
varBatchToMatch = ActiveCell.Offset(0, 2).Value
varQuantToMatch = ActiveCell.Offset(0, 3).Value
Call Check4Match
End Sub

Public Sub Check4Match() 'Check if row from sheet1 match row from sheet2
If varAAcode = varAAcodeToMatch And varHU = varHUToMatch And varBatch = varBatchToMatch And varQuant = varQuantToMatch Then
Worksheets("sheet1").Select 'Get address of row on sheet1
varSheet1MatchRow = ActiveCell.Address
Worksheets("sheet2").Select 'Get address of row on sheet2
varSheet2MatchRow = ActiveCell.Address
'Enter details of rows matched
Worksheets("sheet1").Select
ActiveCell.Offset(0, 7).Value = "Match found on sheet2: " & varSheet2MatchRow 'Enters the details of sheet2 row onto sheet1
Worksheets("sheet2").Select
ActiveCell.Offset(0, 7).Value = "Match found on sheet1: " & varSheet1MatchRow 'Enters the details of sheet1 row onto sheet2
Call Sheet1PositionPlus1 'Match found : Goto next line of sheet1
Else
Call Sheet2PositionPlus1 'No match found: Move to next line on sheet2 and start next search
End If
End Sub

Public Sub Sheet1PositionPlus1() 'Add one count position to sheet1
Sheets("Sheet1").Select
varSheet1Position = varSheet1Position + 1
ActiveCell.Cells(varSheet1Position, 1).Select
If varSheet1Position > (varSheet1Count + 1) Then 'If end of sheet1 then close program, all items checked
Call EndScript
Exit Sub
Else
varAAcode = ActiveCell.Value
varHU = ActiveCell.Offset(0, 1).Value
varBatch = ActiveCell.Offset(0, 2).Value
varQuant = ActiveCell.Offset(0, 3).Value
varSheet2Position = 1 'Reset sheet2 to line 1 (Start search from the top)
Call SheetPositionForSheet2
End If
End Sub

Public Sub Sheet2PositionPlus1() 'Add one count position to sheet2
Sheets("Sheet2").Select
varSheet2Position = varSheet2Position + 1
ActiveCell.Cells(varSheet2Position, 1).Select
If varSheet2Position > varSheet2Count + 1 Then
MsgBox "End"
End
Call Sheet1PositionPlus1 'End of sheet2 goto next line on sheet1 and start search again from next row on sheet1
Exit Sub
Else
varAAcodeToMatch = ActiveCell.Value
varHUToMatch = ActiveCell.Offset(0, 1).Value
varBatchToMatch = ActiveCell.Offset(0, 2).Value
varQuantToMatch = ActiveCell.Offset(0, 3).Value
Call Check4Match
End If
End Sub

Public Sub EndScript()
End 'End Script
End Sub[/vba]

[mancubus]

hi.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=966

http://www.exceltip.com/st/Compare_t...Excel/477.html