Compare Problem
 

Hi Folks,

i'm working on comparing column A in two workbooks and having the
differences reported back either to a sheet in workbook1 or a new workbook.
The code below runs without error but does not return any values and i know
there are differences which should be reported.

There is probably something wrong with my comparison but I'm not sure what
it is.

What i'd like to happen is for the macro to find values in column A that do
not appear in both workbooks and return the values for column A, B, and C.

Dim xlsFilePath As String
xlsFilePath = Application.GetOpenFilename
Application.Workbooks.Open xlsFilePath

MsgBox ("Path & File is " & xlsFilePath)
MsgBox ("File is " & ActiveWorkbook.Name)

Set mstrBook = Workbooks("NJ Pay Rate Check.xls")
Set mstrSheet = mstrBook.Sheets("Rate Check")

Set cmprBook = Workbooks(ActiveWorkbook.Name)
Set cmprSheet = cmprBook.Sheets("NJ Union Contract Raise Report")

Set newbk = Workbooks.Add
Set newbk_sht = newbk.Sheets("Sheet1")
NewbkRowCount = 1

With mstrSheet
LastRow = .Range("A200")
For RowCount = 6 To LastRow
If .Range("A" & RowCount) < "" Then
empNum = .Range("A" & RowCount)
With cmprSheet
Set c = .Columns("A:A").Find(what:=empNum, _
LookIn:=xlValues, lookat:=xlWhole)
End With

If c Is Nothing Then
.Rows(RowCount).Copy _
Destination:=newbk_sht.Rows(NewbkRowCount)
NewbkRowCount = NewbkRowCount + 1
End If
End If
Next RowCount
End With

Any help is greatly appreciated.

Compare Problem
 

Just a guess but this is not going to work out for you

LastRow = .Range("A200")
is the same as
LastRow = .Range("A200").Value
which fills lastrow with whatever the value of cell A200 is...

You could change your code to
For RowCount = 6 To 200

Additionally you really should declare your variables...
--
HTH...

Jim Thomlinson


"Stephen" wrote:

Hi Folks,

i'm working on comparing column A in two workbooks and having the
differences reported back either to a sheet in workbook1 or a new workbook.
The code below runs without error but does not return any values and i know
there are differences which should be reported.

There is probably something wrong with my comparison but I'm not sure what
it is.

What i'd like to happen is for the macro to find values in column A that do
not appear in both workbooks and return the values for column A, B, and C.

Dim xlsFilePath As String
xlsFilePath = Application.GetOpenFilename
Application.Workbooks.Open xlsFilePath

MsgBox ("Path & File is " & xlsFilePath)
MsgBox ("File is " & ActiveWorkbook.Name)

Set mstrBook = Workbooks("NJ Pay Rate Check.xls")
Set mstrSheet = mstrBook.Sheets("Rate Check")

Set cmprBook = Workbooks(ActiveWorkbook.Name)
Set cmprSheet = cmprBook.Sheets("NJ Union Contract Raise Report")

Set newbk = Workbooks.Add
Set newbk_sht = newbk.Sheets("Sheet1")
NewbkRowCount = 1

With mstrSheet
LastRow = .Range("A200")
For RowCount = 6 To LastRow
If .Range("A" & RowCount) < "" Then
empNum = .Range("A" & RowCount)
With cmprSheet
Set c = .Columns("A:A").Find(what:=empNum, _
LookIn:=xlValues, lookat:=xlWhole)
End With

If c Is Nothing Then
.Rows(RowCount).Copy _
Destination:=newbk_sht.Rows(NewbkRowCount)
NewbkRowCount = NewbkRowCount + 1
End If
End If
Next RowCount
End With

Any help is greatly appreciated.

Compare Problem
 

Thanks for the reply but I already know this is not working for me, which is
why I'm turning to the experts for assistance.

I think I'm going about my solution the wrong way but I'm not familiar
enough the vba code to develop my solution on my own...

2 workbooks - 1 is MASTER the other is WEEKLY.
* I do know the name of the sheet in each that I want to compare, BUT I want
to compare column a values in each workbook/sheet and list any instance where
the column A value does not appear in both books and list the value from
column A, B, & C. it does not matter to me if the list is on a new workbook
or a specific sheet in the MASTER workbook.

So I think it's a two-way compare that I'm trying to accomplish.

Thank you to all with advise!

"Jim Thomlinson" wrote:

Just a guess but this is not going to work out for you

LastRow = .Range("A200")
is the same as
LastRow = .Range("A200").Value
which fills lastrow with whatever the value of cell A200 is...

You could change your code to
For RowCount = 6 To 200

Additionally you really should declare your variables...
--
HTH...

Jim Thomlinson


"Stephen" wrote:

Hi Folks,

i'm working on comparing column A in two workbooks and having the
differences reported back either to a sheet in workbook1 or a new workbook.
The code below runs without error but does not return any values and i know
there are differences which should be reported.

There is probably something wrong with my comparison but I'm not sure what
it is.

What i'd like to happen is for the macro to find values in column A that do
not appear in both workbooks and return the values for column A, B, and C.

Dim xlsFilePath As String
xlsFilePath = Application.GetOpenFilename
Application.Workbooks.Open xlsFilePath

MsgBox ("Path & File is " & xlsFilePath)
MsgBox ("File is " & ActiveWorkbook.Name)

Set mstrBook = Workbooks("NJ Pay Rate Check.xls")
Set mstrSheet = mstrBook.Sheets("Rate Check")

Set cmprBook = Workbooks(ActiveWorkbook.Name)
Set cmprSheet = cmprBook.Sheets("NJ Union Contract Raise Report")

Set newbk = Workbooks.Add
Set newbk_sht = newbk.Sheets("Sheet1")
NewbkRowCount = 1

With mstrSheet
LastRow = .Range("A200")
For RowCount = 6 To LastRow
If .Range("A" & RowCount) < "" Then
empNum = .Range("A" & RowCount)
With cmprSheet
Set c = .Columns("A:A").Find(what:=empNum, _
LookIn:=xlValues, lookat:=xlWhole)
End With

If c Is Nothing Then
.Rows(RowCount).Copy _
Destination:=newbk_sht.Rows(NewbkRowCount)
NewbkRowCount = NewbkRowCount + 1
End If
End If
Next RowCount
End With

Any help is greatly appreciated.