How can this IF, OR function be improved

[targa72]

Is there a better way to do this function?
=-IF(OR(NOW()>DATE(YEAR(NOW()),9,17),(getDataFromCreditCards($A13,$B56,$C56)>-('2012'!AE13))),getDataFromCreditCards($A13,$B56,$C56),-('2012'!AE13))

Here’s what it is doing.

1. Checking todays date.
2. If the date is 9/17 or greater then it gets data to fill the cell
3. The data comes from “CreditCards” data base, using A23 (credit card name), B56 (starting date) and C56 (ending date).
4. If the date is before 9/17 it checks last year’s input on sheet “2012” to the current data in the “CreditCards” data base and inputs the larger of the two.
5. Also all inputs are a negative number.

I’ve used this for over 10 years and it works but there are two annoying problems.

1. Every year when adding a new sheet all occurrences of this need changing to the sheet it is looking at.
2. it’s slow! With five credit cards and twelve months, that’s 60 of these checks to go through after every input.

[Norie]

Can you post the code for getDataFromCreditCards?

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the adress bar)

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[targa72]

Here is the code for getDataFromCreditCards.

Please Login or Register  to view this content.

[B]Moderators note: code tags added for you

[Norie]

What is the function meant to return, a string or numeric?

[targa72]

Numeric. Good catch. Don’t know why I wrote the While loop that way.

Here’s the new and improved loop.

Please Login or Register  to view this content.

Moderators note: code tags added for you - this time

[FDibbins]

Targa, please use code tags when posting VBA codes. I will add them this time

[Norie]

targa

If the function is to return the sum of Amount then you could use SQL's SUM function, then you wouldn't need to loop.

That should speed things up.

[targa72]

Adding SUM to the SQL did not work. It was still slow and did not return a value.

I think the problem is in the way I wrote the IF statement. If the date is before 9/17 it compares the getDataFromCreditCards to last year’s input when it should just put in last year’s cell figure. Comparing getDataFromCreditCards to last year’s input needs to take place after 9/17 only.

[targa72]

Adding SUM to the SQL did not work. It was still slow and did not return a value.

I think the problem is in the way I wrote the IF statement. If the date is before 9/17 it compares the getDataFromCreditCards to last year’s input when it should just put in last year’s cell figure. Comparing getDataFromCreditCards to last year’s input needs to take place after 9/17 only.

[Norie]

How exactly did you try SUM in the SQL?

[targa72]

strSQL = "SELECT SUM (Amount) AS ReturnSet FROM tblTransactions " & _
"INNER JOIN tblCreditCard " & _
"ON tblCreditCard.CreditCardID = tblTransactions.CreditCardID " & _
"WHERE CardName LIKE '%" & card_type & "%' AND " & _
"((PostDate) >= #" & start_date & "# and (PostDate)<= #" & ending_date & "#) ;"

rs.Open strSQL, cn
getDataFromCreditCards = ReturnSet

[targa72]

Thank you Norie for your help.
The problem was twofold, the OR part of the IF statement was forcing the getDataFromCreditCards to fire every time any cell was changed and there were to many of those IF statements. I moved all the IF statements to a separate workbook and removed the OR part and completely rewrote the IF statement.
Now when the first workbook opens it opens the second workbook updates then saves and closes the second workbook.
The whole workbook runs faster (except for opening) after any cell input.