-
Excessively slow script (Update wanted)
[VBA]S
Sub ChangeTest()
'These are your variables
'Do not change these
Dim ArchiveOld
Dim ArchiveNew
Dim lCount As Long
'This is where the script starts
Range("E6").Select
'This is your timer
lCount = 0
'this is the amount of times the script will run
'Change number to the amount of cells
Do Until lCount = 225
'Set Variables to cells and then
'compare them to each other
Set ArchiveNew = ActiveCell
ActiveCell.Offset(0, 1).Select
Set ArchiveOld = ActiveCell
If ArchiveNew = ArchiveOld Then
ArchiveNew.Interior.ColorIndex = 10
Else
ArchiveNew.Interior.ColorIndex = 6
End If
ActiveCell.Offset(0, -1).Select
ActiveCell.Offset(1, 0).Select
'Add 1 to your counter
'This should reloop lcount-1 amount of times
lCount = lCount + 1
Loop
'This brings your pointer back up to the top of the screen
Range("A1").Select
End Sub
[/VBA]
-
It is better to use a For() or For Each routine to loop. You could use the Range() with Offset(). Select is usually the slowest method.
You can use or get ideas to speed up at: http://www.vbaexpress.com/kb/getarticle.php?kb_id=1035
-
Yeah that would probably help.
What about getting around the .Select routine?
I wrote this myself.. I don't know any other function or class that will do this type of work for me..
Any other comments would be appreciated.
-
Updated to the For() loop. No results.
[VBA]Sub ChangeTest()
'These are your variables
'Do not change these
Dim ArchiveOld
Dim ArchiveNew
Dim lCount As Long
'This is where the script starts
Range("E6").Select
'This is your timer
'this is the amount of times the script will run
'Change number to the amount of cells
For lCount = 0 To 10
'Set Variables to cells and then
'compare them to each other
Set ArchiveNew = ActiveCell
ActiveCell.Offset(0, 1).Select
Set ArchiveOld = ActiveCell
If ArchiveNew = ArchiveOld Then
ArchiveNew.Interior.ColorIndex = 10
Else
ArchiveNew.Interior.ColorIndex = 6
End If
ActiveCell.Offset(1, -1).Select
'Add 1 to your counter
'This should reloop lcount-1 amount of times
Next lCount
'This brings your pointer back up to the top of the screen
Range("A1").Select
End Sub[/VBA]
More updates needed
-
Much better. [vba]Sub ChangeTest()
'These are your variables
'Do not change these
'The fewer variables, the less strain on your system memory.
'Neglegible in this situation, but good practice nonetheless.
Dim lCount As Long
'This is where the script starts
'Avoid the .Select method except for how it is used at the end of this procedure.
'Range("E6").Select
'Defer updating the display until finished making changes.
'Screen updating is a big drain on speed.
Application.ScreenUpdating = False
'This is your timer
'this is the amount of times the script will run
'Change number to the amount of cells
For lCount = 0 To 10
'Compare cells systematically, using the counter variable
'to increment down the sheet.
If Cells(6 + lCount, 5) = Cells(6 + lCount, 6) Then
Cells(6 + lCount, 5).Interior.ColorIndex = 10
Else
Cells(6 + lCount, 5).Interior.ColorIndex = 6
End If
Next lCount
'This ensures your pointer is at the top of the screen
Range("A1").Select
'Update your display
Application.ScreenUpdating = True
End Sub[/vba]
Last edited by ntrauger; 06-07-2011 at 02:00 PM.
Reason: vba tags need a little tweaking
--Nate
-
Yes, speed has increase!!
Nate would you have any suggestions for doing this in excel other than
Comparing each one individually?
I had to write this myself because I don't know of a function that does it.
- Mike
-
Conditional formatting would be a much better approach.
-
I agree but the amount of cells being updated needs changed every once and a while an conditional formating in excel 2003 only does one cell at a time..
I wish there was a way to do an array..
-
Not sure what you mean exactly, but if you create the conditional formatting for the first cell and remove the "$" from the cell addresses, you can copy and paste or fill the formatting down the whole column.
-
-
Same here.
In E5, set conditional formatting rules as follows.
Condition 1: Cell Value Is...equal to..."=F5" (Green fill)
Condition 2: Cell Value Is...not equal to..."=F5" (Yellow fill)
Hit okay, and drag the fill handle down to cover the rest of the cells you are concerned with in column E.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules