Vlookup or Match VBA

A

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.
 
J

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
 
A

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.
 
J

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
 
A

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
 

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