Problem with MATCH Formula for different book by variable name

[Pam]

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]

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

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]

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

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

 

[Tom Ogilvy]

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

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

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

 

[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.)

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

 

[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

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

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

 

[Jay]

Hi Tom –

Thanks for opening my eyes; Pam’s code structure is perfectly fine and
Dave’s 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, there’s absolutely nothing wrong
with the “workbook_name = ...†syntax.

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

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

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

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]

Hi Dave –

My apologies for the misleading information I provided. As was obvious to
you, Pam’s 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 didn’t waste much of your time.

--
Jay

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

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]

Hi Pam –

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

 

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

Hi Dave –

My apologies for the misleading information I provided. As was obvious to
you, Pam’s 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 didn’t waste much of your time.

--
Jay

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

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