Skip
# Stumped by VBA-ising an array formula in Excel

(adsbygoogle = window.adsbygoogle || []).push({});

Post

# Stumped by VBA-ising an array formula in Excel

October 17, 2008 11:20 AM Subscribe

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.

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.

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

posted by Mokusatsu at 11:36 AM on October 18, 2008

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

posted by Mokusatsu at 11:36 AM on October 18, 2008

How about the Find method?

posted by Mike1024 at 1:13 PM on October 20, 2008

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).Value End Sub

posted by Mike1024 at 1:13 PM on October 20, 2008

This thread is closed to new comments.

Allow me to introduce you to the Evaluate method: If that doesn't do it, mefi mail me.

posted by Mike1024 at 11:56 AM on October 17, 2008