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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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

Watch MrExcel Video

Forum statistics

Threads
1,123,119
Messages
5,599,821
Members
414,341
Latest member
Mohammedsobhey

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