Subscript Out of Range Run Time Error 9: Please Help

[Pauly723]

Hello,

I'm trying to have the following calculation, Row I (Budget) - Row K (Expenses), populate the answer in a new column (O) for each row of data. The code I have written below gives me a run time error 9: Subscript out of Range and highlights the line starting with Answer5(counter, 1). Can anyone assist? Thanks

Please Login or Register  to view this content.

[PCI]

What do you want to do with statements?

Please Login or Register  to view this content.

At the end of the loop Dimension5 is equal to UBound(Budget, 1) +1
Therefore you have to use UBound(Budget, 1) and not Dimension5 in next Statement

Please Login or Register  to view this content.

[Pauly723]

I'm trying to set dimension 5 as having the capability to include both the range values of the budget and the expenses. I wasn't sure if I could or if I needed to create another dimension. I know dimensions are typically row x column, but I thought dimensions could be expanded beyond just 1 row and 1 column. I'm not sure if my approach is correct or not. Please let me know if this helps or if you need further information. Thanks for your reply

[PCI]

See next remake

Please Login or Register  to view this content.

[PCI]

What do you mean by " the capability to include both the range values of the budget and the expenses"
A first think will be to have a range from
the Maxi (LBound(Expenses, 1) , LBound(Budget, 1))
to
the Min (UBound(Expenses, 1) , UBound(Budget, 1))

[Pauly723]

That helped eliminate the run time error, thank you

However, the data that's being calculated is not accurate. For example it's saying that $2,243.96 - $2,243.96 = ($0.96). Further, I can notice a trend occurring. Every time the answer should be = 0, it''s incorrectly putting the answer as the amount of cents in both the budget and expense (.96 as with the case above), and then making it negative. Every time the answer is anything other than 0, it's calculating correctly. Do you have any idea why this might be happening?

[PCI]

" the data that's being calculated is not accurate."
Send your data to see what's up

[Pauly723]

My apologies, but what's the most effective way to send the data to you?

[PCI]

"$2,243.96 - $2,243.96 = ($0.96)."
Yes of course because you are using

Please Login or Register  to view this content.

which truncate the Budget value removing digits after the "."

[PCI]

"the most effective way to send the data "
see next steps to follow

Reply To Thread
Go Advanced
Manage Attachments
Add Files
Browse
==> Select your file
Upload
Done

[Pauly723]

Interesting, nice catch. Any ideas on an alternative solution?

[PCI]

If you want to do a simple difference just do

Please Login or Register  to view this content.

[Pauly723]

Perfect, that's exactly what I needed. Thank you

May I also ask, if I try to divide instead using the following:

Please Login or Register  to view this content.

I receive a run time error '11': division by zero. Do you know how I can fix this?

Thanks again

[PCI]

In case Expenses(Counter, 1) = 0
what is the result you want?

[Pauly723]

Can it just resume with the next calculation?

I tried "On Error Resume Next", but that didn't work.

[PCI]

Try

Please Login or Register  to view this content.

[PCI]

Another variation

Please Login or Register  to view this content.