Formula Array Help in VBA

  • Thread starter Thread starter Hans Hamm
  • Start date Start date
H

Hans Hamm

I am much better with formulas than VBA so, I tried this formula inside VBA.... without {} it does not give me the correct data, with {} VBA fails.
This is one part of the formula, the same formula repeats 11 times looking at different columns for new project data
Does anyone have a work around??

=IF(Z3=""N/A"","""",IF(Z3=""OOS"",Z$1&""-""&""OOS"",Z$1&""-""&IF(ISERROR(INDEX(Sheet3!$N$2:$N$10000,MATCH(1,($A3=Sheet3!$C$2:$C$10000)*(Sheet4!Z$1=Sheet3!$B$2:$B$10000),0))),Z3,INDEX(Sheet3!$N$2:$N$10000,MATCH(1,($A3=Sheet3!$C$2:$C$10000)*(Sheet4!Z$1=Sheet3!$B$2:$B$10000),0)))))"
 
I am much better with formulas than VBA so, I tried this formula inside VBA... without {} it does not give me the correct data, with {} VBA fails.

This is one part of the formula, the same formula repeats 11 times looking at different columns for new project data

Does anyone have a work around??



=IF(Z3=""N/A"","""",IF(Z3=""OOS"",Z$1&""-""&""OOS"",Z$1&""-""&IF(ISERROR(INDEX(Sheet3!$N$2:$N$10000,MATCH(1,($A3=Sheet3!$C$2:$C$10000)*(Sheet4!Z$1=Sheet3!$B$2:$B$10000),0))),Z3,INDEX(Sheet3!$N$2:$N$10000,MATCH(1,($A3=Sheet3!$C$2:$C$10000)*(Sheet4!Z$1=Sheet3!$B$2:$B$10000),0)))))"
Click to expand...

I should have added when I try this with formulas for all columns I exceed the # Excel will allow...
 
I think an explination of what your formula is supposed to do will help in determining how to fix it, or provide guidance to the expert vba programmers in here to solve your quandry.
 
I thought I may have to do that, the explanation is a little long, so here goes:
2-Sheets I am working from:
Sheet 3 is the Project Data
Sheet 4 is the Program/Store Data

Using these examples 105 as the store #, Ceiling Tile as the Project, Complete for the Project Status and 2012-11-05 as the date
In Sheet 4 Column H is where all the data will be combined I have this to set it up as such:
"Dim rng7 As Range 'Report Compiled Projects" and "Set rng7 = Sheet4.Range("H2:H" & Range("A" & Rows.Count).End(xlUp).Row)"


Here is where the fun starts; a store (represented by column A) can have 0-26 different projects (which is an entirely different issue). The projects start in column Z.
So, ***"=IF(Z3=""N/A"","""",IF(Z3=""OOS"",Z$1&""-""&""OOS""*** tells me if the project in Sheet 4 Column Z is either: N/A- no project or OOS-Out Of Scope

If it is then determined that the project is neither OOS or N/A then I need to find the status of that Project. This information is stored on Sheet 3
Now I need the Project Status: INDEX(Sheet3!$N$2:$N$10000
Match that with the Store: MATCH(1,($A3=Sheet3!$C$2:$C$10000)
Match that with the Project Name: *(Sheet4!Z$1=Sheet3!$B$2:$B$10000),0)))
AND if the match to the Project Name is an error then:
Z3,INDEX(Sheet3!$N$2:$N$10000,MATCH(1,($A3=Sheet3!$C$2:$C$10000)*(Sheet4!Z$1=Sheet3!$B$2:$B$10000),0)))))
Therefore the possible results in Column H would be:
Blank for the 1st If
Ceiling Tile-OOS for the 2nd If
Ceiling Tile-Complete for the 3rd If
Ceiling Tile-2012-11-05 for the 4th If

Then I need to do this anywhere from 1-26 times over columns AA, AD, BA etc...
Does this help in explaining it?

Thanks
 
Back
Top