# Can't get array formula to work as needed.

#### bbhgroup

##### Board Regular
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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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"?

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...

Replies
1
Views
306
Replies
1
Views
110
Replies
17
Views
522
Replies
0
Views
137
Replies
3
Views
232

Threads
1,221,493
Messages
6,160,139
Members
451,624
Latest member
TheWes

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back