Problems with worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Page1 is a worksheet.

You tried this:
Sheets("rpt1").Select
Set Page1 = ActiveSheet.Name

Name is just a string--not a worksheet.

You wanted something like:
Sheets("rpt1").Select
Set Page1 = ActiveSheet

or just this.
set Page1 = Sheets("rpt1")

And watch your typing:
If Page1.Cells(i, "A").Value = Page2.Cells(n, "A").Vale Then
should be
If Page1.Cells(i, "A").Value = Page2.Cells(n, "A").Value Then
(.Value at that far right side)

I also wouldn't use a variable named Count. It looks way too much like VBA's
..Count property (like in .rows.count). It may not confuse VBA, but it would
confuse me.

And just to make things less confusing, I'd use variables that are named
something like:

Dim Rpt1Wks as worksheet
dim Rpt2Wks as worksheet
dim OutWks as worksheet

dim Rpt1LastRow as long
dim Rpt2LastRow as long

I find that adding a little more info to the variables makes it easier to see
what's going on.

But there can be quicker ways to look for matches.

In excel, you can use =match() to see if (and where) a match occurs in a single
column (or row).

=match(a1,sheet2!a1:a99,0)
will return an error if there is no match or a number indicating what row in
that range held the match.

You can use this kind of thing within your code, too:

Option Explicit
Sub compareItems2()
Dim Rpt1Wks As Worksheet
Dim Rpt2Wks As Worksheet
Dim OutWks As Worksheet

Dim Rpt1Rng As Range
Dim Rpt2Rng As Range
Dim myCell As Range

Dim res As Variant

Dim DestCell As Range
Dim HowManyCols As Long

Set Rpt1Wks = Worksheets("rpt1")
Set Rpt2Wks = Worksheets("rpt2")
Set OutWks = Worksheets.Add 'create a new worksheet each time!

HowManyCols = 13
'copy over headers from rpt1
OutWks.Range("a1").Resize(1, HowManyCols).Value _
= Rpt1Wks.Range("a1").Resize(1, HowManyCols).Value
'get ready for first difference
Set DestCell = OutWks.Range("a2")

With Rpt1Wks
'this is the range the code will loop through
Set Rpt1Rng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Rpt2Wks
'this is the range that the code will look for a match
Set Rpt2Rng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In Rpt1Rng.Cells
'same as that =match() worksheet function
res = Application.Match(myCell.Value, Rpt2Rng, 0)
If IsError(res) Then
'not found, skip it???
Else
'.offset(0,2) <- same row, two columns to the right.
If myCell.Offset(0, 2).Value = Rpt2Rng(res).Offset(0, 2).Value Then
'same value in column C, so skip it
Else
'all 13 values at once
DestCell.Resize(1, HowManyCols).Value _
= Rpt2Rng(res).Resize(1, HowManyCols).Value
'come down for the next difference
Set DestCell = DestCell.Offset(1, 0)
End If
End If
Next myCell

End Sub
 
Back
Top