Improve speed of For i Loop ? Is is possible to use arrays

[mohamadmh]

H

 

[combo]

First of all, if there are no formulas in processed range, I would assign values to variant array, process the array and finally return values to worksheet:
[pre]Dim v, rngData As Range
Set rngData = Range("G2:AE" & lastRow)
v = rngData
' process v
' ...
' return data to range
rngData = v[/pre]

BTW:
1.
What is the logic behind [tt]I = I + 1: Next I[/tt]?
2.
The flow in the big [tt]If ... End If[/tt] block is terminated when the first condition is satisfied. You process V, W, X and Y columns, in a specific order, is it intended to skip other tests after first True?

combo

 

[mohamadmh]

Thank you combo for the suggestions. Could you help modify the code?

 

[combo]

After (if AE is last column):
[tt]Dim v, rngData As Range
Set rngData = Range("G2:AE" & lastRow)
v = rngData[/tt]
You have all data from range in 2D array. Just try to do the same with entries in v (for testing I would use smaller range). The last line, [tt]rngData = v[/tt], returns whole processed array to range.

combo

 

[mohamadmh]

thank you again combo, but not an expert and would struggle to complete.

Thank you again for your help

 

[SkipVought]

but not an expert and would struggle to complete

Each one of the "experts" here at Tek Tips or anywhere else in the world, were at one time NOT an expert in whatever skill they are currently "expert". And it took TIME and EFFORT to achieve whatever level of expertise has been accomplished and TIME and EFFORT to maintain that level of expertise as well.

In the path to expertise is STRUGGLE. The struggle is necessary and essential. It is part of a greater principle of sowing and reaping. Everything you sow (good or bad) will eventually come to fruition. I can't determine if coding VBA and using Excel is something that you need now or in the future, but from someone who was introduced to Excel in the early 1990s and desired to use Excel as a tool in my career, I'd affirm that the time and effort were worth investing, along with lots and lots of other post college studying I've done.

It all takes time, effort and occasional struggle.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[mohamadmh]

Thanks SkipVought and understand. Appreciate you posting reply.

 

[Andrzejek]

First, please edit your original post (again) because all what we can see in your post is "H" :-(

You VBA code is not very complicated, so I would definitely try to use Formulas instead. I would start with just two simple formulas in columns T and U since your outcome is based just on the values in columns I and J. And see if formulas would be faster than your VBA code for just these 2 columns:

For I = 2 To lastRow
    Select Case Cells(I, "G").Value
        Case [red]"IRS"[/red]
            Select Case Cells(I, "J").Value
                Case [red]"BUY"[/red]
                    Cells(I, "T").Value = [red]"Client"[/red]
                    Cells(I, "U").Value = [red]"Dealer"[/red]
                Case [red]"SELL"[/red]
                    Cells(I, "T").Value = [red]"Dealer"[/red]
                    Cells(I, "U").Value = [red]"Client"[/red]
            End Select
        Case Else
            Select Case Cells(I, "J").Value
                Case [red]"BUY"[/red]
                    Cells(I, "T").Value = [red]"Dealer"[/red]
                    Cells(I, "U").Value = [red]"Client"[/red]
                Case [red]"SELL"[/red]
                    Cells(I, "T").Value = [red]"Client"[/red]
                    Cells(I, "U").Value = [red]"Dealer"[/red]
            End Select
    End Select
Next I

I know: "If all you have is a hammer, everything looks like a nail", so if you have your heart set on VBA, I am sure combo’s suggestion will work faster. But what else you could try is to use Constants for “IRS”, "BUY", "SELL", "Client", "Dealer", etc. That may improve the performance, who knows…?

Processing 500,0000 rows in Excel will take some time no matter what you do, but I would guess the data is not stored in Excel. I would assume you have it in some type of a data base (Access? SQL Server? Oracle?). Processing 500,0000 records in a well-designed, relational data base is a matter of a few Update statements that take seconds (or less).


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

>Processing 500,0000 records in a well-designed, relational data base

Yep, struck me that this might be better handled in a proper database management system.

And I also thought that perhaps using formulas might be a better approach.

 

[Andrzejek]

The flow in the big [tt]If ... End[/tt] If block is terminated when the first condition is satisfied. You process V, W, X and Y columns, in a specific order, is it intended to skip other tests after first True?

All conditions end up populating column Z with either [tt]"Fallback"[/tt] or [tt]"Primary"[/tt].
But you are right, just because one criteria returns "Primary" - there could be other condition(s) that may return "Fallback" for the same row.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson