+ Reply to Thread
Results 1 to 3 of 3

Complex nested array formula problem and challenge

  1. #1
    Registered User
    Join Date
    05-15-2006
    Posts
    16

    Complex nested array formula problem and challenge

    Hi, firstly this could be put into the Functions or General forums, but ultimately I need this coded up in VBA so I'll leave it here. Also, I have the function I want, but there seems to be a problem with entering it via VBA. Anyhoo, on to the problem:

    I want to store data in a range of cells which contains different Equipment types, and numbers thereof - with multiple types specified in a single cell. The format is semi-colon and comma delimited, for example;

    A1: Equip1, 5; Equip2, 8; Equip4, 3;
    A2: Equip3, 1; Equip4, 7; Equip1, 7;

    and so on.

    Now I have built a function which reads in the string, searches for the Equipment label (eg "Equip1"), finds the corresponding number and then retrieves this string section. Here is my formula, if you can follow it (please advise if you want me to break it down):

    =IF(ISERROR(SEARCH(A7, $B$4)), 0, MID($B$4, SEARCH(A7, $B$4)+LEN(A7)+2, IF(ISERROR(SEARCH(";",$B$4,SEARCH(A7, $B$4)+LEN(A7)+2)), LEN($B$4)+1, SEARCH(";",$B$4,SEARCH(A7, $B$4)+LEN(A7)+2))-(SEARCH(A7, $B$4)+LEN(A7)+2)))

    In this case A7 holds the search text ("Equip1") and B4 holds the data string.

    I then expanded it to an array formula so I can read across many data cells:

    {=SUM(IF(ISERROR(SEARCH(A25, $B$20:$B22)), 0, VALUE(MID($B$20:$B22, SEARCH(A25, $B$20:$B22)+LEN(A25)+2, IF(ISERROR(SEARCH(";",$B$20:$B22,SEARCH(A25, $B$20:$B22)+LEN(A25)+2)), LEN($B$20:$B22)+1, SEARCH(";",$B$20:$B22,SEARCH(A25, $B$20:$B22)+LEN(A25)+2))-(SEARCH(A25, $B$20:$B22)+LEN(A25)+2)))))}

    Now if you are astute, you will notice this has 8 levels of nesting. When I wrote it I was ignorant of Excel's limitations of only 7 allowed levels. But, if I type this in to Excel it works fine.

    I am happy to live with the knowledge that what I have since learned has told me this is impossible, but somehow it works. But the problem I have is when I try to code this in VBA as an .arrayformula I get an error and it won't work.

    So my question is this: either can somebody offer me a way to get VBA to write such a function into Excel or, failing that, is there a simpler way to write this function???

    Thanks very much in advance.
    Peter.

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Peter

    My thought would be to build your own function and work from there.

    Put the following in a general module in the sheet with your existing formula.

    Please Login or Register  to view this content.
    Using your array formula as a guide, enter the function on the same sheet as

    =myfunc(B20:B22,A25)

    In my limited testing, I came up with the same numbers.

    This should allow you to more easily generate the formula in VBA, or use the function within the code directly.

    HTH

    rylo

  3. #3
    Registered User
    Join Date
    05-15-2006
    Posts
    16
    OMG that's so cool! Thanks rylo - I had no idea you could generate functions and call them from Excel like that. It's a *much* simpler solution than what I was going for.

    Cheers muchly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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