+ Reply to Thread
Results 1 to 3 of 3

OFFSET+INDIRECT+MATCH formula in VBA.

  1. #1
    Registered User
    Join Date
    03-10-2010
    Location
    kampala, uganda
    MS-Off Ver
    Excel 2007
    Posts
    15

    OFFSET+INDIRECT+MATCH formula in VBA.

    Dear All,
    attached are two result and data source sheets. For this question I'll request your attention to the values highlighted red in the 'Result Sheet'. These values for each corresponding PI Name marked yellow are got from their corresponding tables in the 'Data Sheet'. The values are obtained with help from the offset+indirect+match combination formula below.

    = OFFSET(INDIRECT("[Data_Sheet.xls]SAA_CBA!$C"&MATCH(B3,'C:\bryen\040510\[Data_Sheet.xls]SAA_CBA'!$A$1:$A$51,0)+1),0,0)

    Can anyone help me apply this formula to all the cells using a macro? This would help avoid typing the formula in each cell.
    Thx for all help...
    bryen
    Attached Files Attached Files
    Last edited by bryenwalt; 05-05-2010 at 04:28 AM. Reason: a clearer explanation...

  2. #2
    Registered User
    Join Date
    03-10-2010
    Location
    kampala, uganda
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: OFFSET+INDIRECT+MATCH formula in VBA.

    Hi All, so far i have not recieved any help on this. After a considerable time I've found a workaround to this problem, which i should share.
    in the offset formula, the double quotes need to be replaced with quadruple quotes, see below:
    Formula as inputed directly into table cells:
    = OFFSET(INDIRECT("[Data_Sheet.xls]SAA_CBA!$C"&MATCH(B3,'C:\bryen\040510\[Data_Sheet.xls]SAA_CBA'!$A$1:$A$51,0)+1),0,0)

    Formula as inputed into table cells via a macro (change activecell to your required cell):
    activecell.value= "=OFFSET(INDIRECT(" & """" & "[Data_Sheet.xls]SAA_CBA!$C" & """" & "&MATCH(B3,[Data_Sheet.xls]SAA_CBA!$A$1:$A$51,0)+1),0,0)"

    Note that there is also no need for the URL.
    Hope that helps someone.

  3. #3
    Registered User
    Join Date
    03-10-2010
    Location
    kampala, uganda
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: OFFSET+INDIRECT+MATCH formula in VBA.

    A quick reason to the need for double quotes in the above solution:
    the macro tries to dump the formula as a string, but gets an error when it encounters the second double quotes, after the word "INDIRECT(". We need to add other strings to our formula though, thus the need for: & """" &.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1