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