Sum If Array Formula give False Output Instead of Answer

[Kaustubh1040]

Dear Sir,

What is wrong in this macro code? I am getting 'False' instead of answer in AE column.

Please Login or Register  to view this content.

P.S: This formula work fine without macro. This is how it's look when use directly in the excel file.


=SUMIF('[Sell Out.xlsx]Sheet1'!$B:$B,"="&B2,'[Sell Out.xlsx]Sheet1'!$J:$J)

And, this is working fine.

Please help

Regards
Kaustubh

[Marc L]

Hi !

So just compare the VBA final string with the real cell formula …

[Marc L]

If the VBA string is correct, try with FormulaArray property …

[bakerman2]

Works fine like this.

Please Login or Register  to view this content.

[jindon]

Try change to

Please Login or Register  to view this content.

[Kaustubh1040]

Formula is working now but it is not giving the right answer. Now, I am getting #Value error on using formula post suggested changes. Without "=" and '&' sign (before B2), I am not getting the correct answer.
Please help.

Regards

[Marc L]

So the formula within the VBA code seems to not be correct.
First apply manually a formula in a cell and once the result is the expected one, share it !
It would be far easier to understand what's wrong on VBA side …

[Kaustubh1040]

Finally it worked. Thanks everyone for the help.

There were two error as mentioned below ( will help someone who face the same problem)

1. Add ""="", double quotes before and after equal to sign.
2. I was trying to apply the above formula in closed workbook. Hence it was not working. It work fine when I add the code to open the given file.

Once again, thanks everyone for the help.

[bakerman2]

The formula works as well without the "=" sign.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[dangelor]

As an aside, to isolate a filename from the full path of a closed file., you could use Dir function...

Please Login or Register  to view this content.