Jay,

These statements are invalid for capturing the name of a workbook.

If HistoryDialog is a
Sheet Codename or
Userform Name

and Active_Workbook1 and Active_Workbook2 are textboxes or other controls on
that sheet or userform that could return the name of a workbook selected by
the user, why wouldn't that construct be a legitimate way to get the workbook
names?

What scenario were you envisioning?

--
Regards,
Tom Ogilvy





"Jay" wrote:

Hi Pam -

The name recognition problem you experienced was associated with the
following two statements in your code:

workbook_name = HistoryDialog.Active_Workbook1
workbook_name2 = HistoryDialog.Active_Workbook2

These statements are invalid for capturing the name of a workbook. So, you
would have a problem anywhere you referred to the variables "workbook_name"
or "workbook_name2". Also, there were other syntax errors in various
locations, for example, the way your MATCH formula was structured. Study the
code below to see a more standard and valid approach to capturing and using
workbook names.

I've refashioned your code so that it executes, but I suspect it won't
achieve your goal because that goal is unclear to me. Study and test-run the
code below and adapt it if you can, but don't hesitate to repost for a
follow-up.

Sub Pam()
n = 7

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim Ac As String
Dim Bc As String

Set wb1 = Application.Workbooks("April_Invoices.xls") 'Substitute name of
first open workbook
Set wb2 = Workbooks("March_Invoices.xls") 'Substitute name of second open
workbook

Ac = "CurrentSheet"
Bc = "PreviousSheet"

Set ws1 = Workbooks("April_Invoices.xls").Worksheets(Ac)
Set ws2 = Workbooks("March_Invoices.xls").Worksheets(Bc)

wb1.Activate
With ws1.Range("BL2").Resize(n, 1)
.Formula = "=MATCH(A2, [" & wb2.Name & "]" & ws2.Name & "!A:A,0)"
Range("BL2").Select
For Each itm In .Cells
If Not WorksheetFunction.IsNA(itm) Then
MsgBox ("There is a Match****")
Exit For
End If
Next 'itm
End With

wb2.Activate
With ws2.Range("BL2").Resize(n, 1)
.Formula = "=MATCH(A2, [" & wb1.Name & "]" & ws1.Name & "!A:A,0)"
Range("BL2").Select
For Each itm In .Cells
If Not WorksheetFunction.IsNA(itm) Then
MsgBox ("There is a Match****")
Exit For
End If
Next 'itm
End With

End Sub

--
Jay


"Pam" wrote:

Hello:

I am having a pr blem with the MATCH formula searching a book by a
varibale name. I have tried everything I could think of before asking
for help. After hours
of searching for an answer I'm asking anyone who could solve this
puzzle for me.


I have two different books, one book I want to match spreadsheet to
another spreadsheet in another book, The problem is my variable name
is not being regonized workbook_name2

I have two listboxes which I activate, here is a snipet of my code.


Dim workbook_name As String
Dim workbook_name2 As String
Dim Ac As String
Dim Bc As String
workbook_name = HistoryDialog.Active_Workbook1
workbook_name2 = HistoryDialog.Active_Workbook2
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Ac = "Currentsheet"
Bc = "PreviousSheet"

Windows(workbook_name).Activate
Windows(workbook_name2).Activate
Set WS1 = Workbooks(workbook_name).Worksheets(Ac)
Set WS2 = Workbooks(workbook_name2).Worksheets(Bc)


WS1.Range("BL2").Resize(n, 1).Formula =
"=MATCH(A2,'[workbook_name2]Previoussheet'!A:A,0)"
Range("BL2").Select
MsgBox ("There is a Match****")
'End If

WS2.Range("BL2").Resize(n, 1).Formula =
"=MATCH(A2,'[workbook_name]Currentsheet!A:A,0)"
Range("BL2").Select


Can anyone help mewith this
Thnaking you in advance

Pam