Can't get array formula to work as needed.

[bbhgroup]

I have a formula that looks across every 9th column and if there is a "1" in any of them it returns a "1" as a value, otherwise a zero.
Here is that array formula:

code

={IF(SUMPRODUCT(INDIRECT(CONCATENATE(ADDRESS(5,8),":",ADDRESS(5,1000))), IF(MOD((COLUMN(INDIRECT(CONCATENATE(ADDRESS(5,8),":",ADDRESS(5,1000))))- COLUMN(INDIRECT(ADDRESS(5,8)))+2),9) = 0,1,0)),1,0)}

/code

I am programming the formula using VBA. You will notice the hard coded "5" in the formula, that is the formula for row 5. I currently use a VBA loop to set the formula for 1000 rows and change the row number each time. With 100 records its not so bad, with 1000 it is too slow. I wanted to change the "5" to use the ROW() function, then the formula would be the same for all rows and I could set the formula using a VBA range, but if I put ROW() in place of the row number "5" it causes a #Value error.

Can anyone suggest an improvement?
Thanks

 

[Weaver]

Just a thought

If you're using VBA anyway, wouldn't it be easier to do your checks in code also (with application.screenupdating = false) rather than use this lengthy (and presumably slow) formula?

Maybe you could use the range.find function to check the required columns for the "1"?

 

[bbhgroup]

Thanks, I am looking at this. I don't really need a dynamic calculation after all, good suggestion.

Do I still need to write a loop so the VBA can build the search range for the .find function? Said another way, to you have a quick way to build a find function that searches every 9th column?

The way I would do it without help would be something like a vb loop to create a range:
Set sRange = Range(a1,a10,a18,a27,a36,a45...)
Then look for it (I think the syntax is right.)

Range(srange).Find("1")

I am afraid for 1000 rows that building the sRange would be slow as well...