Problem with MATCH Formula for different book by variable name
 

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

Problem with MATCH Formula for different book by variable name
 

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

Problem with MATCH Formula for different book by variable name
 

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

Problem with MATCH Formula for different book by variable name
 

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

Problem with MATCH Formula for different book by variable name
 

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

Problem with MATCH Formula for different book by variable name
 

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

Problem with MATCH Formula for different book by variable name
 

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

Problem with MATCH Formula for different book by variable name
 

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

Problem with MATCH Formula for different book by variable name
 

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

Problem with MATCH Formula for different book by variable name
 

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