Excel VBA function returning #VALUE! unexpectedly (and only occasionally)

[novaraz]

Hello all,
This one has me stumped. This simple function compares data from a table, and adds values if the input string matches data in the first column of that table:

Please Login or Register  to view this content.

This worked beautifully when I first wrote it. Now, after adding new rows to the table "Balances", any sums corresponding to those new lines break the function: #VALUE! is returned, and the script doesn't ever hit

Please Login or Register  to view this content.

(a breakpoint set there never triggers). However, any lines in the table from when I originally wrote this return the correct sum! Anybody have any ideas?? FYI, Win7 and Office2007 Enterprise.

Thanks!

[AlphaFrog]

I'm not sure what the problem is. I suspect it has to do, in part, with the data.

Can I ask why you don't just use the SUMIF function?
=SUMIF(A:A,Account,C:C)

[jaslake]

Hi novaraz

Welcome to the Forum!!!

Set your Break Point higher in the Code and step through it; perhaps here.

Please Login or Register  to view this content.

or here

Please Login or Register  to view this content.

The Code is not getting to your existing Break Point...something's making it skip that Line of Code.

[novaraz]

I don't use SUMIF because I do more complicated calculations under this loop. The problem is in the equality statement; a break doesn't trigger for any of the new 'accounts', even if the function is pointing to the element being compared...

If I change the contents of an original table row, the function works fine. If I change both a old row and a new row (so should result in the sum of their values in column C), it fails, after successfully comparing the values in the 'old' row.

[AlphaFrog]

I don't use SUMIF because I do more complicated calculations under this loop. The problem is in the equality statement; a break doesn't trigger for any of the new 'accounts', even if the function is pointing to the element being compared...

If I change the contents of an original table row, the function works fine. If I change both a old row and a new row (so should result in the sum of their values in column C), it fails, after successfully comparing the values in the 'old' row.

Can you attach an example file with a data table?

[novaraz]

I just sanitized my workbook, and if course it works fine now. Including if I add new rows to the table. Let me double check all the references in my original workbook, I'll post when I figure it out.

[jaslake]

Check your "unsanitized" Table for it's scope...perhaps you need something like this...

Please Login or Register  to view this content.

[novaraz]

So strange, everything seems to work now. The only thing I changed was the formula input on the worksheet, so that it used table notation instead of cell address. Thanks for the help. I'm hesitant to mark the thread SOLVED, since I'm not sure what changed and why it works.

[joeu2004]

So strange, everything seems to work now. The only thing I changed was the formula input on the worksheet, so that it used table notation instead of cell address. Thanks for the help. I'm hesitant to mark the thread SOLVED, since I'm not sure what changed and why it works.

Wild guess.... Some of the "numbers" might have been entered into Excel as text initially. But when you made changes, you might have inadvertently "re-entered" them, for example by causing Excel to re-interpret the cell contents as truly numeric. This is not unusual if we copy text (e.g. from a webpage) and paste directly into Excel.