R
RSteph
I've got two worsheets (rpt1 & rpt2) with a list of information (Id, Name,
Quantity). I'd like to do a comparison on like values to see if the quantity
for each matches. If they don't then I want to display them on a third page
(outcome) so that I can look for a reason they don't match. I'm having a
problem with my script though... I've tried a few different means of
selecting the page I want, but none of it seems to work. I get an error
saying "Object required" but it doesn't show me where exactly my problem is.
If anyone can help me with some direction I would greatly appreciate it.
Here's my script:
Sub compareItems()
Dim Page1 As Worksheet 'Create a variable to hold the first worksheet.
Dim Page2 As Worksheet 'Create a variable to hold the second worksheet.
Dim Page3 As Worksheet 'Create a variable to hold the outcome worksheet.
Dim LastRow As Long 'Used to hold the value of last row on the external
sheet.
Dim LsRow As Long 'Used to hold the value of last row on the external
sheet.
'Set the sheet the macro was run from as mfFile.
Sheets("rpt1").Select
Set Page1 = ActiveSheet.Name 'prt1 page
Sheets("rpt2").Select
Set Page2 = ActiveSheet.Name 'rpt2 page
Sheets("outcome").Select
Set Page3 = ActiveSheet.Name 'Outcome page
Dim count As Integer 'Holds the row for Outcome page
Dim i As Integer 'Used to incriment For Loop.
Dim n As Integer 'Used to incriment For Loop.
count = 2
i = 0
n = 0
ofLastRow = Page1.Cells(Page1.Rows.count, "A").End(xlUp).Row 'Get the
last row on the page.
ofLsRow = Page2.Cells(Page1.Rows.count, "A").End(xlUp).Row 'Get the last
row on the page.
For i = 2 To ofLastRow 'Loop through to the end of the rpt1 page.
For n = 2 To ofLsRow 'Loop through to the end of the rpt2 page.
If Page1.Cells(i, "A").Value = Page2.Cells(n, "A").Vale Then
If Page1.Cells(i, "C").Value <> Page2.Cells(n, "C").Value Then
Page3.Cells(count, "A").Value = Page2.Cells(n, "A").Value
Page3.Cells(count, "B").Value = Page2.Cells(n, "B").Value
Page3.Cells(count, "C").Value = Page2.Cells(n, "C").Value
Page3.Cells(count, "D").Value = Page2.Cells(n, "D").Value
Page3.Cells(count, "E").Value = Page2.Cells(n, "E").Value
Page3.Cells(count, "F").Value = Page2.Cells(n, "F").Value
Page3.Cells(count, "G").Value = Page2.Cells(n, "G").Value
Page3.Cells(count, "H").Value = Page2.Cells(n, "H").Value
Page3.Cells(count, "I").Value = Page2.Cells(n, "I").Value
Page3.Cells(count, "J").Value = Page2.Cells(n, "J").Value
Page3.Cells(count, "K").Value = Page2.Cells(n, "K").Value
Page3.Cells(count, "L").Value = Page2.Cells(n, "L").Value
Page3.Cells(count, "M").Value = Page2.Cells(n, "M").Value
count = count + 1
Exit For
End If
End If
Next n
Next i 'Increment counter and reloop
End Sub
Quantity). I'd like to do a comparison on like values to see if the quantity
for each matches. If they don't then I want to display them on a third page
(outcome) so that I can look for a reason they don't match. I'm having a
problem with my script though... I've tried a few different means of
selecting the page I want, but none of it seems to work. I get an error
saying "Object required" but it doesn't show me where exactly my problem is.
If anyone can help me with some direction I would greatly appreciate it.
Here's my script:
Sub compareItems()
Dim Page1 As Worksheet 'Create a variable to hold the first worksheet.
Dim Page2 As Worksheet 'Create a variable to hold the second worksheet.
Dim Page3 As Worksheet 'Create a variable to hold the outcome worksheet.
Dim LastRow As Long 'Used to hold the value of last row on the external
sheet.
Dim LsRow As Long 'Used to hold the value of last row on the external
sheet.
'Set the sheet the macro was run from as mfFile.
Sheets("rpt1").Select
Set Page1 = ActiveSheet.Name 'prt1 page
Sheets("rpt2").Select
Set Page2 = ActiveSheet.Name 'rpt2 page
Sheets("outcome").Select
Set Page3 = ActiveSheet.Name 'Outcome page
Dim count As Integer 'Holds the row for Outcome page
Dim i As Integer 'Used to incriment For Loop.
Dim n As Integer 'Used to incriment For Loop.
count = 2
i = 0
n = 0
ofLastRow = Page1.Cells(Page1.Rows.count, "A").End(xlUp).Row 'Get the
last row on the page.
ofLsRow = Page2.Cells(Page1.Rows.count, "A").End(xlUp).Row 'Get the last
row on the page.
For i = 2 To ofLastRow 'Loop through to the end of the rpt1 page.
For n = 2 To ofLsRow 'Loop through to the end of the rpt2 page.
If Page1.Cells(i, "A").Value = Page2.Cells(n, "A").Vale Then
If Page1.Cells(i, "C").Value <> Page2.Cells(n, "C").Value Then
Page3.Cells(count, "A").Value = Page2.Cells(n, "A").Value
Page3.Cells(count, "B").Value = Page2.Cells(n, "B").Value
Page3.Cells(count, "C").Value = Page2.Cells(n, "C").Value
Page3.Cells(count, "D").Value = Page2.Cells(n, "D").Value
Page3.Cells(count, "E").Value = Page2.Cells(n, "E").Value
Page3.Cells(count, "F").Value = Page2.Cells(n, "F").Value
Page3.Cells(count, "G").Value = Page2.Cells(n, "G").Value
Page3.Cells(count, "H").Value = Page2.Cells(n, "H").Value
Page3.Cells(count, "I").Value = Page2.Cells(n, "I").Value
Page3.Cells(count, "J").Value = Page2.Cells(n, "J").Value
Page3.Cells(count, "K").Value = Page2.Cells(n, "K").Value
Page3.Cells(count, "L").Value = Page2.Cells(n, "L").Value
Page3.Cells(count, "M").Value = Page2.Cells(n, "M").Value
count = count + 1
Exit For
End If
End If
Next n
Next i 'Increment counter and reloop
End Sub