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
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:
- How To Select Odd Numbers In Excel?Best solution by Yahoo! Answers
- How To Excel Additional Mathematics?Best solution by Yahoo! Answers
- How to post an array of complex objects (that has an array of complex objects) with jQuery?Best solution by Stack Overflow
- how can i remove an array from an array?Best solution by Stack Overflow
- How to covert csv file to excel and back excel file to csv in python?Best solution by completecampaigns.com
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
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.