Thanks am working on your comments
Hugs & Love
On Mon, Nov 30, 2009 at 4:17 PM, Paul Schreiner <schreiner_p...@att.net>wrote:
> I have a few recommendataions for you.
> ----------------------------------------
> First:
> You didn't mention which version of Excel you are using.
> If it's Excel 97 (2003) then you have 65,536 rows.
> If it's Excel2007, then you have 1,048,576 rows.
>
> Range("n:n") tells your macro to process ALL rows.
> so really, evaluting 65,000 or over a million rows in 3 minutes is pretty
> good!
>
> But, since you probably don't have that much data, you need
> to break it out of the loop when you're done.
>
> You have several ways of doing this depending on your specific data.
> If your column "N" ALWAYS has a value, then you could break it when
> it does NOT:
> if (Cell.Value = "") then Exit For
> If there are sometimes blank cells in column "N" but you have another
> column that always has a value (i.e. column "A"), you could use it instead.
> Range("A:A").Select
>
> then use:
> If Cells(Cell.Row,Cell.Column).Value > .02 and ...
> Or you can count the rows and use a loop counter:
> Nrows = Application.WorksheetFunction.CountA(Range("N:N"))
> For I = 2 to Nrows
> Or:
> Nrows = ActiveCell.SpecialCells(xlLastCell).Row
> -------------------------------------------------
> Next:
> I recommend NOT using an Excel "keyword" as a variable.
> "Cell" is used by Excel to refer to a Cell.
> If, at some point in your future programming you need to use that
> "property".
> Then things like Cell.Cell can be confusing.
> I recommend, first of all, ALWAYS declaring your variables.
> Use "Option Explicit" at the beginning of each module
> so that it forces you to ALWAYS define your variables.
> It also helps to ensure you don't mis-type a variable and accidently
> create a new one!
> Also, name your variables something unique.
> In this case, you could use "tCell"
> -------------------------------------------------
> Next:
> If you have 1000-2000 rows, this isn't going to do much for you,
> but if you're evaluating 10,000 or 20,000 , it makes a difference.
> You're testing 7 "conditions". If the first condition is "true",
> you're STILL testing the other 6.
> Instead, only evaluate them until one is true by using "ElseIf".
> -------------------------------------------------
> Next:
> Clear your color settings at the beginning of your macro.
> Otherwise, when testing, you can't tell if you're doing anything!
> (unless you're clearing it manually)
> -------------------------------------------------
> Using these recommendations, I end up with:
> (I added a timer to calculate the processing time.
> I ran 70000 records in 5.6 seconds)
> -------------------------------------------------
> Now that I think I've helped, I think I can be forgiven for
> a little "rant".
> Normally, I simply delete any message where the person
> couldn't take the time to write a subject line that describes
> the problem. EVERYONE who posts needs "help".
> That doesn't help direct your question that can provide help.
> For instance, I maintain over 140,000 lines of VBA code.
> But I do NOT (currently) use VBA to retrieve data from or update
> Web pages. Any questions relating to that topic, I'll leave
> for someone else. And pivot tables? I'm no expert.
> I can use them, but I'll leave troubleshooting to others with
> more experience. But retrieving data from Oracle, text files,
> or other Excel sources? I'm all over that.
> But "Help" didn't necessarily entice me to have a look.
> But I guess this time it worked.. guess I was bored... (lol)
> -------------------------------------------------
>
> Hope this helps,
> Paul
> -------------------------------------------------
> Option Explicit
> Sub UseColorz()
> Dim tCell, tStart
> tStart = Timer
> Range("N:P").Interior.Color = RGB(255, 255, 255)
> Range("N:N").Select
> For Each tCell In Selection
> If tCell.Value = "" Then
> Exit For
> ElseIf tCell.Value > 0.02 And tCell.Value <= 0.03 Then
> tCell.Interior.Color = RGB(255, 255, 0)
>
> Range("o16").Interior.Color = RGB(255, 255, 0)
> Range("p16").Value = " 0.02 to 0.03"
> ElseIf tCell.Value > 0.03 And tCell.Value <= 0.04 Then
> tCell.Interior.Color = RGB(255, 0, 255)
>
> Range("o17").Interior.Color = RGB(255, 0, 255)
> Range("p17").Value = " 0.03 to 0.04"
> ElseIf tCell.Value > 0.04 And tCell.Value <= 0.05 Then
> tCell.Interior.Color = RGB(0, 255, 255)
>
> Range("o18").Interior.Color = RGB(0, 255, 255)
> Range("p18").Value = " 0.04 to 0.05"
> ElseIf tCell.Value > 0.05 And tCell.Value <= 0.06 Then
> tCell.Interior.Color = RGB(128, 0, 0)
>
> Range("o19").Interior.Color = RGB(128, 0, 0)
> Range("p19").Value = " 0.05 to 0.06"
> ElseIf tCell.Value < 0.01 And tCell.Value <= 0.02 Then
> tCell.Interior.Color = RGB(192, 192, 192)
>
> Range("o21").Interior.Color = RGB(192, 192, 192)
> Range("p21").Value = " 0.059 to 0.066"
> ElseIf tCell.Value > 0.06 And tCell.Value <= 0.07 Then
> tCell.Interior.Color = RGB(153, 153, 255)
>
> Range("o22").Interior.Color = RGB(153, 153, 255)
> Range("p22").Value = " 0.066 to 0.073"
> ElseIf tCell.Value > 0.07 And tCell.Value <= 0.08 Then
> tCell.Interior.Color = RGB(128, 128, 0)
>
> Range("o23").Interior.Color = RGB(128, 128, 0)
> Range("p23").Value = " Greater Than 0.073"
> End If
> Next
> MsgBox Timer - tStart & " seconds "
> End Sub
>
>
>
> ------------------------------
> *From:* janet dickson <janetdicks...@gmail.com>
>
> *To:* excel-macros@googlegroups.com
> *Sent:* Mon, November 30, 2009 3:11:27 AM
> *Subject:* $$Excel-Macros$$ Help
>
>
> Hello Guys
> I have created a useful code for my work, see below. but the problem: This
> code takes almost 3minutes
> to complete running. can anyone help me tweaking this to work much better.
>
> Sub UseColorz()
> Range("n:n").Select
> For Each Cell In Selection
> If Cell.Value > 0.02 And Cell.Value <= 0.03 Then
> Cell.Interior.Color = RGB(255, 255, 0)
> Range("o16").Interior.Color = RGB(255, 255, 0)
> Range("p16").Value = " 0.02 to 0.03"
> End If
> If Cell.Value > 0.03 And Cell.Value <= 0.04 Then
> Cell.Interior.Color = RGB(255, 0, 255)
> Range("o17").Interior.Color = RGB(255, 0, 255)
> Range("p17").Value = " 0.03 to 0.04"
> End If
> If Cell.Value > 0.04 And Cell.Value <= 0.05 Then
> Cell.Interior.Color = RGB(0, 255, 255)
> Range("o18").Interior.Color = RGB(0, 255, 255)
> Range("p18").Value = " 0.04 to 0.05"
> End If
> If Cell.Value > 0.05 And Cell.Value <= 0.06 Then
> Cell.Interior.Color = RGB(128, 0, 0)
> Range("o19").Interior.Color = RGB(128, 0, 0)
> Range("p19").Value = " 0.05 to 0.06"
> End If
> If Cell.Value < 0.01 And Cell.Value <= 0.02 Then
> Cell.Interior.Color = RGB(192, 192, 192)
> Range("o21").Interior.Color = RGB(192, 192, 192)
> Range("p21").Value = " 0.059 to 0.066"
> End If
> If Cell.Value > 0.06 And Cell.Value <= 0.07 Then
> Cell.Interior.Color = RGB(153, 153, 255)
> Range("o22").Interior.Color = RGB(153, 153, 255)
> Range("p22").Value = " 0.066 to 0.073"
> End If
> If Cell.Value > 0.07 And Cell.Value <= 0.08 Then
> Cell.Interior.Color = RGB(128, 128, 0)
> Range("o23").Interior.Color = RGB(128, 128, 0)
> Range("p23").Value = " Greater Than 0.073"
>
> End If
> Next
> End Sub
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
> http://www.excelitems.com
> 2. Excel tutorials at http://www.excel-macros.blogspot.com
> 3. Learn VBA Macros at http://www.vbamacros.blogspot.com
> 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
>
> To post to this group, send email to excel-macros@googlegroups.com
> If you find any spam message in the group, please send an email to:
> Ayush Jain @ jainayus...@gmail.com or
> Ashish Jain @ 26may.1...@gmail.com
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 6,500 subscribers worldwide and receive many nice notes about
> the learning and support from the group. Our goal is to have 10,000
> subscribers by the end of 2009. Let friends and co-workers know they can
> subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
> http://www.excelitems.com
> 2. Excel tutorials at http://www.excel-macros.blogspot.com
> 3. Learn VBA Macros at http://www.vbamacros.blogspot.com
> 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
>
> To post to this group, send email to excel-macros@googlegroups.com
> If you find any spam message in the group, please send an email to:
> Ayush Jain @ jainayus...@gmail.com or
> Ashish Jain @ 26may.1...@gmail.com
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 6,500 subscribers worldwide and receive many nice notes about
> the learning and support from the group. Our goal is to have 10,000
> subscribers by the end of 2009. Let friends and co-workers know they can
> subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
