Stumped by VBA-ising an array formula in Excel

October 17, 2008 11:20 AM

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.

posted by Mokusatsu to computers & internet (3 answers total)

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

If that doesn't do it, mefi mail me.
posted by Mike1024 at 11:56 AM on October 17, 2008

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

How about the Find 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).Value
End Sub

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