Compare ranges n copy at bottom of first range

K

Kaza Sriram

hi ,

I am trying to compare data in two different workbooks.it should
compare data in A column in book 1 with data in data in A column in
book 2.

if there is no match, then it should add those data not matched into
book1 at the last of A column.

here is my code:

Sub master()

Dim b1 As Workbook

Dim w1 As Worksheet

Dim rCell As Range
Dim LookRange As Range
Application.ScreenUpdating = False

MsgBox ("Select the Subset File location")

Set b1 = Workbooks.Open(Application.GetOpenFilename(, , "Open File
1"))
Set w1 = b1.Worksheets("suppliers")

Set LookRange = Sheets("suppliers").Range("A1",
Range("A65536").End(xlUp))

MsgBox LookRange.Address

For Each rCell In LookRange

' here it give subscript out of range error.!!!!

If WorksheetFunction.CountIf _
(Sheets("pros").Columns(1), rCell.Text) = 0 Then
rCell.Range("A2:C1").Copy _
Destination:=Sheets("pros").Range("A65536").End(xlUp).Offset(1, 0)

End If

Next rCell

Application.CutCopyMode = False

Application.ScreenUpdating = True

Sheets("pros").Range("A1:A65536").HorizontalAlignment = xlRight

Sheets("prols").Activate

End Sub


can anyone help in this error, it'll be great...

thanks,

kaza
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top