I have a macro throwing an error and it appears to be due to a size restriction???? HELP

[dangolfs86]

Hello everyone,

I have a macro that is performing a basic 'IF' function. I have included the code below. In case 1 the macro will run properly without fault. In Case 2 the macro throws an error "Run-time error '1004': AutoFill method of Range class failed." The only difference in the two cases is the range, Case 1 is around 2000 rows, Case 2 is around 500,000 cells.

I did not expect to see the error that i did, but can someone confirm if this is just a size of dataset error? Is there any way to fix it?

Case 1:

Please Login or Register  to view this content.

Case 2:

Please Login or Register  to view this content.

[Norie]

The code works fine for me but takes it's time.

By the way, what is the formula meant to do? Is it to see if the values in L10:L500000 are in the range A2:A7?

If it is there's probably a non-array formula you could use.

[dangolfs86]

Yes that is what it is supposed to do. I am fairly new to array formulas, is there a reason why i would not want to use an array formula?

Which version of excel did you use?

[Norie]

Array formulas slow things up especially if you have a lot of them.

If you can then you should use a non-array formula.

I think in this case you could use COUNTIF.

Please Login or Register  to view this content.

[dangolfs86]

ACtually i read this the wrong way. L10 will have a set of values separated by ','. The macro is meant to see if any values from A2:A7 exist in L1:L500000. The issue is that there are ranges within the L10 cells so that if i am looking for 5000 and L10 says 3500-6799, I want the formula to return a 'yes'. If that makes sense.

[Norie]

So does the original formula work?

[dangolfs86]

To an extent, for some reason it will identify the numbers it needs to, but not all the time. It works well enough for me to finish the task, but it's not full-safe.