PDA

View Full Version : Solved: Code Tweak



Philcjr
01-03-2009, 11:48 AM
Is there another way to write this code?

I am looking to increase speed, (HOME PC - 2 secs, WORK LAPTOP = 8+ sec) all updating is turned off. This is for a workbook with my finances, hence why it is not posted.

NOTES: Code, Date and Amount are named ranges and they all go to row 2495 of the same worksheet... so "R" will loop through from 2 to 2495.


For C = 2 To BalCol - 1 'Used for populating Columns B through V
Application.StatusBar = Format(((C - 1) / (BalCol - 1)) * 100, "0.00") & "% Complete..."
For R = 2 To BillCycleRow 'Used for populating Rows
If Range("A" & R).Value = "" Then GoTo ZZ:
sYear = Year(Range("A" & R).Value) & "/"
sMonth = Format(Range("A" & R).Value, "mm")
sFormula = "SumProduct((Code" & "=""" & Cells(1, C).Value & """)*(Date=""" & sYear & sMonth & """)*(Amount))"
Cells(R, C).Value = Evaluate(sFormula)
ZZ:
Next R
Next C


If there is any need information, let me know... I can post the enter sub if need be, but all other code executes very quickly

Kenneth Hobs
01-03-2009, 01:32 PM
Hopefully, you have turned off calculation and such as I detailed in http://vbaexpress.com/kb/getarticle.php?kb_id=1035 (http://vbaexpress.com/forum/../kb/getarticle.php?kb_id=1035) as you said.

Tip: Reference a cell once.
e.g.
Dim cell As Range, cVal
Set cell = Range("A" & r)
cVal = cell.Value
If cVal = "" Then GoTo ZZ:
sYear = Year(cVal) & "/"
sMonth = Format(cVal, "mm")
sFormula = "SumProduct((Code" & "=""" & Cells(1, C).Value & """)*(Date=""" & sYear & sMonth & """)*(Amount))"
Cells(r, C).Value = Evaluate(sFormula)
ZZ:
Next r
Next C

Philcjr
01-03-2009, 02:25 PM
Kenneth,

Thanks for your insights, I am running out the door as soon as I getback (about an hour) I will test and post results.

THANKS
Phil

mikerickson
01-03-2009, 03:07 PM
I think that using native Excel and writting to the spreadsheet twice would be faster than looping.
You might try replacing the entire OP code with
Dim formulaString as String

With ActiveSheet
With Range(.Cells(2, 2), .Cells(BillCycleRow, BalCol - 1))
formulaString = "SUMPRODUCT((code=RC1)*(date=TEXT(RC1,""mm/yyyy"")*(amount)))"
formulaString = "=IF(RC1="""",""""," & formulaString & ")"
.FormulaR1C1 = formulaString
.Value = .Value
End With
End With

Philcjr
01-03-2009, 04:24 PM
Kenneth,
Your coding cut the time in half :thumb

Mike,
When I ran your code, it did not work, however in processed in half the time though... :dunno When you did Code=RC1 it made the formula $A2, $A3,$A4...
The "Code" values that need to match are the column headings, so Code is a range on another worksheet that matches the value found in B1, C1, D1, E1.... I think this is the proplem. If you have any other ideas, I would be happy to tweak and test.

Thanks,
Phil

mikerickson
01-03-2009, 04:52 PM
I suggest you comment out the .Value = .Value line and tweak the resulting formula until it brings the desired result.

The Macro Recorder is a quick way to translate from A1 to R1C1 notation.

Changing the formulaString in the routine would bring you the desired result.

Un-comment out the .Value line once the new formulaString has been vetted.

EXISTANCE THEORM
Claim: There exists an R1C1 formula that, when put in the range, will return the same results as the OP routine.

Each of the elements of the string EVALUATED are the equivalent of some R1C1 formula.

Range("A" & R).Value translates to the spreadsheet formula =RC1
(in A1 notation the absolute/relative row/column would look like =$A3 )

Cells(1, C).Value goes to =R1C
(in A1, something like =D$1 )

sYear goes to = YEAR(RC1) & "/"

sMonth goes to = TEXT(RC1, "mm")

Since all the terms of EVALUATE are the equivalent of spreadsheet formulas, there is an R1C1 formula that will have the same result as the loop.

Side note:
I'd be hesitant to use named ranges date and code, since the name is the same as the worksheet functions DATE and CODE.

Philcjr
01-03-2009, 05:33 PM
Mike,

Got whatcha are saying, but there are two things:

Should be SUMPRODUCT((Code="A1" or "B1" or "C1"...
Now it reads SUMPRODUCT((Code=$A1, $A2, $A3...

and

for the "Date", I went into Excel and replaced the coded formula and put the "TEXT($A1,"yyyy/mm" in quotes ("2006/01") and the formula worked, how can I get the "Date" in quotes?

Thoughts?

mikerickson
01-03-2009, 05:47 PM
Could you post a sample of your worksheet, including the named ranges?

What is the addresses of the cells that have $A1 $A2 $A3 (but should have A$1, B$1, C$1)?
Are they in the same row or in the same column?

You could put Date in double quotes.

msgbox "Today's ""Date"" is Saturday."

mikerickson
01-04-2009, 11:58 AM
As you said in your PM, the formula

=IF($A23="","",SUMPRODUCT((Code=$A23)*(Date=TEXT($A23,"yyyy/mm")*(Amount))))

should be

=IF($A23="","",SUMPRODUCT((Code=S1)*(Date="2006/06")*(Amount)))

To do that, if A23 holds a date in June of 2006, the line in the code should be
formulaString = "SUMPRODUCT((code=R1C)*(date=TEXT(RC1,""yyyy/mm"")*(amount)))"
formulaString = "=IF(RC1="""",""""," & formulaString & ")"
If S1 is the cell that holds that date, the red should be changed to R1C

Philcjr
01-04-2009, 04:26 PM
Mike,

I have played with this and the only thing that does not work is the "Date" in SumProduct. If the Date is not in quotes like this, it does not work:
(Works) =IF($A2="","",SUMPRODUCT((Code=B1)*(Date="2005/02")*(Amount)))

(Does Not Work) =IF($A2="","",SUMPRODUCT((Code=B1)*(Date=2005/02)*(Amount)))

I think I have no choice but to use a loop

If you have any other ideas I will try

Thanks
Phil

mikerickson
01-04-2009, 07:08 PM
How about Date = TEXT(A2,"YYYY/MM") (if A2 is where the 2005/02 comes from)

What cell generates the sYear and sMonth that affects what is goes in S23?

mikerickson
01-06-2009, 07:45 AM
Thanks for e-mailing me the file.
I found the problem!!
The formula needed a litte tweaking but the big issue was that when formulaString was dimmed as String, it gave a circular reference error (???)

Dim formulaString As Variant

With Sheets("roll-up")
With Range(.Cells(2, 2), .Cells(BillCycleRow, BalCol - 1))
formulaString = "SUMPRODUCT((Code=R1C)*(Date=TEXT(RC1,""yyyy/mm""))*(Amount))"
formulaString = "=IF(RC1="""",""""," & formulaString & ")"
.FormulaR1C1 = formulaString
.Value = .Value
End With
End With

Philcjr
01-06-2009, 08:38 AM
Mike,

Thanks for playing around with this. As we can see, there are many ways to skin the cat. :)

Mike's way - Total time to process 4.265 (Average of 5 times)
Kenn's way - Total time to process 4.300 (Average of 5 times)

In the end, both ways are very fast.

Kenneth Hobs
01-06-2009, 08:43 AM
Glad it worked out. In your speed tests, hopefully you used the speedup routines as you said earlier.

mikerickson
01-06-2009, 11:15 AM
Looking at your file, I think that a Pivot Table might be even faster that either of the posted codes.

I've never really looked at Pivot Tables, but your data/goals give me an opportunity to see what they can do.

Philcjr
01-06-2009, 11:35 AM
Kenn,
Yes, I use something like your "SpeedUp" routines and these test were on my work laptop... and as you can see, the time was cut in half (almost)

Mike,
Interesting, I am good with Pivot Tables... I will play around with this idea and see where I end-up. It is funny, that I have been using this file for a couple of years and I didnt even think to use a Pivot Table, I use them everywhere else. Thanks for this suggestion.