Vlookup or Match VBA
 

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.

Vlookup or Match VBA
 

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" wrote:

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.

Vlookup or Match VBA
 

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" wrote:

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.

Vlookup or Match VBA
 

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.

"Joel" wrote:

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" wrote:

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.

Vlookup or Match VBA
 

Joel,

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

"Joel" wrote:

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" wrote:

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.

Vlookup or Match VBA
 

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





"akemeny" wrote:

Joel,

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

"Joel" wrote:

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" wrote:

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.

Vlookup or Match VBA
 

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





"akemeny" wrote:

Joel,

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

"Joel" wrote:

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" wrote:

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.

Vlookup or Match VBA
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"akemeny" wrote in message
...
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.

"Joel" wrote:

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" wrote:

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.

Vlookup or Match VBA
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"akemeny" wrote in message
...
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.

"Joel" wrote:

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" wrote:

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.

Vlookup or Match VBA
 

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
on changes that could be made to either so that it will pick up on the
accounts that are absent & the accounts that are present?

"Joel" wrote:

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





"akemeny" wrote:

Joel,

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

"Joel" wrote:

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" wrote:

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.

Vlookup or Match VBA
 

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
on changes that could be made to either so that it will pick up on the
accounts that are absent & the accounts that are present?

"Joel" wrote:

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





"akemeny" wrote:

Joel,

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

"Joel" wrote:

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" wrote:

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.