Vlookup or Match VBA

[akemeny]

What I need to do is fairly simple yet I can't find anything that will allow
me to do it. I have two workbooks. In workbook1 I have almost 20 worksheets
with account information. In workbook2 I have two worksheets, one blank.

What I want to do is create either a macro or a function that will look at
the account sheet in workbook2, then look at ALL the sheets in workbook1 and
find the accounts from workbook2 that do not appear on any of the sheets in
workbook1. The accounts from workbook2 that do not appear in workbook1 I
need copied to the blank sheet in workbook2.

Is this possible?

Any help would be greatly appreciated.

 

[Joel]

It is easier to go through every sheet in book2 and copy the rows that don't
match the accounts in book1 sheet1. I assumed the account numbers are in
column A. change code as required.


Sub FindMissAccounts()

Set LookupSht = Workbooks("Book1.xls").Sheets("Sheet1")
Set DestupSht = Workbooks("Book1.xls").Sheets("Sheet2")
Set AccountBk = Workbooks("Book2.xls")

NewRow = 1
For Each sht In AccountBk.Sheets
With sht
RowCount = 1
Do While .Range("A" & RowCount) <> ""
Account = .Range("A" & RowCount)
Set c = LookupSht.Columns("A").Find(what:=Account, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Rows(RowCount).Copy _
Destination:=DestupSht.Rows(NewRow)
NewRow = NewRow + 1
End If
RowCount = RowCount + 1
Loop
End With

Next sht

End Sub

 

[akemeny]

This works great Joel. Thank you for your help. I have one question about
it though. Is there a way to do an "For Each Except"? In the Book2 workbook
there is also a summary page that I do not need to have considered. I looked
through the excel help setup, but couldn't find anything on exclusions or
exceptions.

 

[akemeny]

Joel,

Is there a way to do this same process in just a Vlookup function?

 

[Joel]

Using vlookup from a worksheet would be complicated and require a number of
manual steps.

I added an IF statement to the code to skip the page you don't want to
process.

Sub FindMissAccounts()

Set LookupSht = Workbooks("Book1.xls").Sheets("Sheet1")
Set DestupSht = Workbooks("Book1.xls").Sheets("Sheet2")
Set AccountBk = Workbooks("Book2.xls")

NewRow = 1
For Each sht In AccountBk.Sheets
if sht.name <> "Except" then
With sht
RowCount = 1
Do While .Range("A" & RowCount) <> ""
Account = .Range("A" & RowCount)
Set c = LookupSht.Columns("A").Find(what:=Account, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Rows(RowCount).Copy _
Destination:=DestupSht.Rows(NewRow)
NewRow = NewRow + 1
End If

RowCount = RowCount + 1
Loop
End With
End if
Next sht

End Sub

 

[Don Guillett]

if sht.name<>"Summary" then
code
end if

 

[akemeny]

Thanks for your help on this.

I have a vlookup type formula that I'm trying to use for a different set of
spreadsheets, but I can't figure out why its not calculating correctly. I've
tried it two different ways:

=vlookup(A2,'[Book1.xls]Summary of Findings'!$A:$AF,20,False)

&

=IF(ISNA(MATCH(A2,'[Book1.xls]Summary of Findings'!
$A:$AF,20)),"absent","present")

Both of them just mark everything as missing. Do you have any suggestions