How I can find string in excel with vba?

Stumped by VBA-ising an array formula in Excel

• How to do a VBA version of the following array formula in Excel?The following array formula (entered with ctrl,shift,enter) will produce the string 'Bank statement'!H842, which is the name of a cell I would like to be able to manipulate via VBA:{="'Bank statement'!H"&MATCH(C1&D$39,'Bank statement'!A:A&'Bank statement'!C:C,0)} It creates a string out of the values in the cells C1 and D$39 and looks those up in an array made out of the A and C columns of the sheet 'Bank statement'. Its function is to find the row of a payment from a certain payer on a certain day, and then return the address of the cell in column H of that row. i.e. it will find the payment from Joe Bloggs on the 15/3/2008 for example, and return the address of the cell in column H on the row that payment appears. This formula will put the address of that cell, which I'd like to use as a range variable in VBA. I'm completely stumped by trying to make it work though. I've tried for a day or two to do it, asked every Excel VBA related usenet and Yahoo group and not gotten a solution. I'm more confident in MeFi though. :) A macro I can run from a button click will suffice for now, but if I could have my every wish granted I'd also like to be able to make a pivot table hyperlink-clickable, so I could click on any entry in that pivot table (pivot table consists of payments arranged by date and payer) and navigate to that payment.

• Answer:

  I don't have your exact spreadsheet, so I'll go ahead and assume the formula you have there works fine. Allow me to introduce you to the http://msdn.microsoft.com/en-us/library/aa223886(office.11).aspx method:Sub Macro1()'' Macro1 Macro' Macro recorded 17/10/2008 by' matchLineNum = Evaluate("MATCH(C1&D$39,'Bank statement'!A:A&'Bank statement'!C:C,0)") cellLocationString = "'Bank statement'!H" & matchLineNum MsgBox cellLocationString MsgBox Range(cellLocationString).ValueEnd SubIf that doesn't do it, mefi mail me.