Match & Copy ranges on multiple workbooks 1

[imagoodwon]

How do I structure VBA code to do the following:

I have 2 workbooks, bk1 & bk2
From bk2 Col A, look at bk1 col A
If there's a match on Col A, compare the range at bk 2 ActiveCell.Offset(0, 1).Resize(1, 5) and compare it to the corresponding range on bk1
If there's NO MATCH on Col A, then copy the entire row to Col A bk1
bk1 gets all the changes and updates.
At the end, show a MsgBox with how many matches were made & how many rows were copied.
Eventually, I will create a new workbook, 2 Sheets to record the new additions ( Sheet1) and the changes (Sheet2)

 

[SkipVought]

Please post an example of the data you are comparing in both workbooks in columnar format.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[imagoodwon]

bk1:

Code # Name Rate Agnt Start
002-00 Peter 2% Mr. Woo 1/2/2009
002--01 Paul 8% Ms.Komu 1/3/2009
002-02 Mary . 2/3/2009
002--03 . Sun. Phd 2/4/2006

..............................................
bk2:

Code # Name Rate Agnt Start
002-00 Peter 2% Mr. Woo 1/2/2009
002-01 Paul 8% Ms.Komu 1/3/2009
002-02 Mary 11% Dr. Omato 2/3/2009
002-03 Gulaian 8% Sun. Phd 2/4/2006
002-07 Marion 6% Mme. Lavoom 5/6/2005
002-08 Nadia . . .
. McGoo 5% Pancho 3/4/1999
. Bilal 3% Almerado 3/2/2009

 

[imagoodwon]

Is there a way that you can attach worksheets? If so, how? Where?

Thanks

 

[SkipVought]

In bk2, you have missing values (or a PERIOD) in [Code #].

What does that mean?

Which book is the source & destination?

In this statement, where is the ActiveCell?

ActiveCell.Offset(0, 1).Resize(1, 5)

I do not see 5 columns of data, let alone 6, including column A.

So is a NO MATCH, just based on column A or EACH data item in the row?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[imagoodwon]

The 5 columns are
Code # Name Rate Agnt Start

Yes, some cells in bk2 will not have data. The period was there to assist with the formatting. Those should be blank cells.

bk1 is the original sheet and bk2 has the new changes. I need to update bk1 with information from bk2.
bk1 is the destination for the new data.
The code is most likely wrong. Here's the process I'm hoping for.

First look for a match in Col A. If yes, check the range that extends 4 rows to the right. Compare the cells in that range in bk2 to the matching cells in bk1. If different, copy the range to bk1

If there was no match on Col A, then copy the entire row from bk2 to bk1.

Show a MsgBox with how many matches were made & how many rows were copied

 

[SkipVought]

Do you have any code at all? Please post.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[imagoodwon]

I've got some sample code modified where I can select the new range that I want to compare with the old range. What is the correct VBA format to compare 2 ranges and see if they're equal.
r1 = c2.Offset(0, 1).Resize(1, 5)
r2 = newfind.Offset(0, 1).Resize(1, 5)

I tried

If r1 <> r2 Then ' VBA GIVES ME AN ERROR HERE
chg = chg + 1

Thanks

 

[SkipVought]

You cannot compare Range Objects. You must compare each value within each range object

dim r11 as range, r21 as range, bMatch as boolean

r1 = c2.Offset(0, 1).Resize(1, 5)
r2 = newfind.Offset(0, 1).Resize(1, 5)

bMatch = true
for each r11 in r1
  if r11.value <> r21.cells(r11.row, r11.column).value
     bMatch = False
     exit for
  then 
next
if not bmatch then
  r1.copy destination:=r2.Cells(1,1)
  NoMatchCounter = NoMatchCounter + 1
else
  MatchCounter = MatchCounter + 1
end if

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[imagoodwon]

Hi Thanks:

No matter how I've reworked this line, I cant seem to get past it

if r11.value <> r21.cells(r11.row, r11.column).value

That's where I keep getting an error

 

[SkipVought]

Sorry, the previous code was untested.

Dim r11 As Range, bMatch As Boolean, r1 As Range, r2 As Range
Dim iCol As Integer

Set r1 = [A5].Offset(0, 1).Resize(1, 5)
Set r2 = [A7].Offset(0, 1).Resize(1, 5)

bMatch = True
iCol = 1
For Each r11 In r1
  If r11.Value <> r2.Cells(1, iCol).Value Then
     bMatch = False
     Exit For
  End If
  iCol = iCol + 1
Next
If Not bMatch Then
  r1.Copy Destination:=r2.Cells(1, 1)
  NoMatchCounter = NoMatchCounter + 1
Else
  MatchCounter = MatchCounter + 1
End If

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]