Compare Cells in Two Workbooks

[msnyc07]

Is it possible to have a script that does the following;

Compares all the Cells in a specific column in WorkBookA to all Cells in a
specific column in multiple sheets in WorkbookB

If a match is found in B, remove the entire Row in WorkbookA

Thanks in advance for any help

Michael

 

[Otto Moehrbach]

Does workbook A have multiple sheets? If it does, what sheet do you want to
work with? What column in workbook A?
What sheets in workbook B? All of them? Do you want to check just cell for
cell or do you want to check if the value is in any cell in the specific
column in the pertinent sheets in workbook B? What version of Excel are you
using? HTH Otto

 

[msnyc07]

Hi, thanks for the reply. Answers to your queries follow;

1) Workbook A has one Sheet. I want to compare FROM Column A in that Sheet

2) Workbook B has ~100 Sheets. I want to compare TO Column A in every sheet
in that Workbook

I want to start with WorkBookA:A1 and then search in every ColumnA n
WorkBookB to see if there is a match.

If Match:
Remove AN-DN in WorkbookA (e.g. if it found a match for WorkbookA:A100 in
WorkbookB (in any Column A) it would removed WorkbookA:A100-D100

If No Match:
Compare next WorkBookA:AN+1

The additional parameter is that in WorkBookB the Cells can contain more
info then just the string searching for so it would have to be something like;

WorkBook:AN String CONTAINED in WorkbookB:AN (so if there was additional
information in that cell it would still be a match.

I am using Excel 2007

Hope this helps

 

[Otto Moehrbach]

I think I have enough to work with, but I want to clarify one thing. If I
am looking for the contents of A100 in wb A, do you want me to search the
WHOLE column A of every sheet in wb B or just cell A100? Otto

 

[Otto Moehrbach]

When you say " removed WorkbookA:A100-D100...", do you mean you want the
entire row deleted or do you want A100100 cleared/erased? Otto

 

[Otto Moehrbach]

Michael
Try this macro. Place this macro in a regular module in the workbook
that has the search words in Column A. I call that workbook "wb A". As
written, this macro assumes that you have a workbook named "B.xls". Change
this as needed. It also assumes that both workbooks are open. This macro
loops through each cell in Column A of wb A, and searches for the contents
of each such cell in Column A of every sheet in wb B. If found, it will
clear A of that row in wb A. Note: It will not delete the row. Come
back if you need more. Otto
Sub FindColA()
Dim wbA As Workbook, wbB As Workbook, ws As Worksheet
Dim ColAwbA As Range, ColAwbB As Range, i As Range
Set wbA = ThisWorkbook
Set wbB = Workbooks("B.xls")
Set ColAwbA = Range("A1", Range("A" & Rows.Count).End(xlUp))
Application.ScreenUpdating = False
wbB.Activate
For Each i In ColAwbA
For Each ws In ActiveWorkbook.Worksheets
With ws
Set ColAwbB = .Range("A1", .Range("A" &
Rows.Count).End(xlUp))
If Not ColAwbB.Find(What:=i.Value, LookAt:=xlPart) Is
Nothing Then
i.Resize(, 4).ClearContents
Exit For
End If
End With
Next ws
Next i
wbA.Activate
Application.ScreenUpdating = True
End Sub