Can't get array formula to work as needed.

bbhgroup

Board Regular
Joined
Dec 30, 2008
Messages
97
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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

Board Regular
Joined
Dec 30, 2008
Messages
97
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...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,706
Messages
5,833,233
Members
430,198
Latest member
KitaYama

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

Which adblocker are you using?

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
Top