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.

Mokusatsu at Ask.Metafilter.Com Visit the source

Was this solution helpful to you?

Other answers

It worked, thanks. Now more out of interest than necessity, I may need to do similar stuff again in the future, is there a pure VBA method I could be using which doesn't just execute the formula? Travis

Mokusatsu

How about the http://msdn.microsoft.com/en-us/library/aa195730(office.11).aspx method?Sub Macro2()'' Macro2 Macro' Macro recorded 17/10/2008 by' whatToFind = Range("C1").Value & Range("D39").Value Dim findResult As Range Set findResult = Range("'Bank statement'!A:A&'Bank statement'!C:C").Find(whatToFind) MsgBox findResult.AddressLocal MsgBox Range("'Bank statement'!H" & findResult.Row).ValueEnd Sub

Mike1024

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.