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

Without knowing what's in those variables, maybe....

WS1.Range("BL2").Resize(n, 1).Formula _
= "=MATCH(A2,'[" & workbook_name2 & "]Previoussheet'!A:A,0)"

(Same kind of thing for both matches.)

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

--

Dave Peterson

On Mar 22, 6:14 am, Dave Peterson wrote:
Without knowing what's in those variables, maybe....

WS1.Range("BL2").Resize(n, 1).Formula _
= "=MATCH(A2,'[" & workbook_name2 & "]Previoussheet'!A:A,0)"

(Same kind of thing for both matches.)





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

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Hi Dave;

I knew it had something to do with the (&) but I just couldn't get the
correct combination. Your
anwer to my question worked the way I wanted my code to!! Again
thank you so much


Pam

Hi Dave €“

My apologies for the misleading information I provided. As was obvious to
you, Pams code was perfectly structured and needed only a minor adjustment.
As described to the other posters, I made an erroneous assumption up front
and that led to the misguided (and misguiding) approach to a €œsolution.€� I
sincerely hope it didnt waste much of your time.

--
Jay


"Dave Peterson" wrote:

Without knowing what's in those variables, maybe....

WS1.Range("BL2").Resize(n, 1).Formula _
= "=MATCH(A2,'[" & workbook_name2 & "]Previoussheet'!A:A,0)"

(Same kind of thing for both matches.)

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

--

Dave Peterson

That's the good thing about newsgroups. If I post a misguided response (and I
think it's happened once--maybe twice <vbg), there's always someone there to
correct it.

Don't feel bad, you've just joined a pretty big club.



Jay wrote:

Hi Dave €“

My apologies for the misleading information I provided. As was obvious to
you, Pams code was perfectly structured and needed only a minor adjustment.
As described to the other posters, I made an erroneous assumption up front
and that led to the misguided (and misguiding) approach to a €œsolution.€� I
sincerely hope it didnt waste much of your time.

--
Jay

"Dave Peterson" wrote:

Without knowing what's in those variables, maybe....

WS1.Range("BL2").Resize(n, 1).Formula _
= "=MATCH(A2,'[" & workbook_name2 & "]Previoussheet'!A:A,0)"

(Same kind of thing for both matches.)

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

--

Dave Peterson


--

Dave Peterson

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

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

On Mar 22, 8:15 am, Tom Ogilvy
wrote:
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- Hide quoted text -

- Show quoted text -

Hi Jay:


The reason I did it that way is because the macro is associated with
another tool I wrote and the workbook name changes everyday because
the current days date is part of work book name. That is thr reason I
cannot use a hard coded name it is chosen by the drop down list box by
user.The ("&---&") works for what I have to do. That is what I
needed. Thank You for your response. Each book has two sheets with
the same name and I needed to pick book to show difference in current
vs previous sheet.
Again thank you for your response.

Pam

Hi Pam €“

Your code was perfectly structured; my response was based on an erroneous
assumption that you were trying to do something you werent. I sincerely
hope that my misguiding information didnt waste much time and that youre on
to other things.

--
Jay

Hi Tom €“

Thanks for opening my eyes; Pams code structure is perfectly fine and
Daves patch is totally on target.

I had been working in Access and the minor syntax problem in her original
MATCH statement triggered the thought that she was trying to manipulate
workbook_name as a workbook object. With that erroneous assumption, I was off
on a hay-wired path to the dingweeds with the rest of my €œsolution€�; I was
convinced that her choice to capture the workbook name was invalid in the
context of this assumption. As you note, theres absolutely nothing wrong
with the €œworkbook_name = ...€� syntax.

My sincere apologies for the waste of time and thank you for the follow-up.
--
Jay


"Tom Ogilvy" wrote:

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