sum if formula works as array but running non-stop as VBA

**Akbarov** · 12-15-2018, 07:44 PM

Hey community,

Please Login or Register  to view this content.

I use this formula = [sum(if((isnumber(match(list1,market,0)))*(list2>0)*(list1<>"")*(not(isnumber(match(list4,list3,0)))),printed))] as array it works fine, but when i try it with VBA it is running non-stop
AP:AP is from AP1 to AP31 numeric
E:E is full list

Can anybody help me please?

**jindon** · 12-15-2018, 09:34 PM

Please Login or Register  to view this content.

ridiculous...

**Akbarov** · 12-16-2018, 03:50 AM

what do you mean?

**jindon** · 12-16-2018, 04:52 AM

Ask in the Formula forum.

**Akbarov** · 12-16-2018, 04:53 AM

ok but as formula it works fine..

**jindon** · 12-16-2018, 04:56 AM

Then why don't you enter a formula in the cell?

**Akbarov** · 12-16-2018, 04:59 AM

because customers will use this file, they might delete formula by mistake

**jindon** · 12-16-2018, 05:04 AM

1) Normally formula in the cell is the fastest.
2) You can always convert formula to a value after the calculation, so no formula remains.

**AliGW** · 12-16-2018, 05:08 AM

Originally Posted by jindon

Please Login or Register  to view this content.

ridiculous...

Frankly, Jindon, this is of no help whatsoever. Please explain why you have made the comment. The point about these forums is to support and educate people, not to deride them. We all make mistakes when learning - there is no shame in that. The OP has asked for VBA, not a formula - either help him with his request or do not participate any further in this thread.

**AliGW** · 12-16-2018, 05:09 AM

Originally Posted by Akbarov

because customers will use this file, they might delete formula by mistake

I know you do not wish to use a formula, but please remember that cells containing formulae can be locked and protected to prevent accidental erasure.

**jindon** · 12-16-2018, 05:10 AM

Started from here.
VBA sumif How can i indicate range?

**AliGW** · 12-16-2018, 05:13 AM

I have asked you to explain your comment in post #2: please do so.

**jindon** · 12-16-2018, 05:14 AM

If you read the link, you will understand.

**AliGW** · 12-16-2018, 05:16 AM

Do as you have been asked and explain the comment here, please.

**jindon** · 12-16-2018, 05:20 AM

Originally

Please Login or Register  to view this content.

Now

Please Login or Register  to view this content.

Named range for whole column and use in an array formula is ridiculous.

**Akbarov** · 12-16-2018, 05:23 AM

anyways will use array formula and protect sheet, jindon helped me too, problem is because i am newbie with bad english.

**jindon** · 12-16-2018, 05:27 AM

You can do like

Please Login or Register  to view this content.

So that formula should not remain.

**AliGW** · 12-16-2018, 05:44 AM

Thank you.

**SjMaxwell** · 12-16-2018, 07:52 AM

It might be taking a long time because your ranges are so large. Try this, it narrows your ranges. If your lists don't start in row 1 you will need to change the 1s to the row your data starts in.

Please Login or Register  to view this content.

**Akbarov** · 12-16-2018, 08:13 AM

i tryed your code but i get #N/a result

when i change match(list4,list3,0) to match(list3,list4,0) i get 125443 which is not correct result

**SjMaxwell** · 12-16-2018, 08:21 AM

Well, I didn't change the formula because you said that it worked for you as an array. I'm a newbie like you so I don't think I can help further, but on the bright side the code didn't continue to run forever

**jindon** · 12-16-2018, 08:29 AM

Please Login or Register  to view this content.

**Akbarov** · 12-16-2018, 08:42 AM

you want me to add this to old code? or is that single code?

**jindon** · 12-16-2018, 08:45 AM

replace

Please Login or Register  to view this content.

part with the one I posted.

**Akbarov** · 12-16-2018, 09:04 AM

Works like a charm... thank you very much

isn't VBA better for huge files? because i need to use same code for 30 cells , array formula will count all at once but VBA 1by1 ? i change and copy paste your VBA is ok right? for all 30

**jindon** · 12-16-2018, 09:52 AM

I don't understand what you are trying to do.

**alansidman** · 12-16-2018, 11:39 AM

crossposted: https://www.excelforum.com/excel-pro...ost5027643Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future

**6StringJazzer** · 12-16-2018, 01:51 PM

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.

sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Re: sum if formula works as array but running non-stop as VBA

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Array formula only works on some cells

[SOLVED] This Array formula works sometimes and not others

[SOLVED] Explaining how array formula works

Bookmarks

Bookmarks

Posting Permissions