×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

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

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

(OP)
H

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

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:
Dim v, rngData As Range
Set rngData = Range("G2:AE" & lastRow)
v = rngData
' process v
' ...
' return data to range
rngData = v 

BTW:
1.
What is the logic behind I = I + 1: Next I?
2.
The flow in the big If ... End 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?

combo

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

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

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

After (if AE is last column):
Dim v, rngData As Range
Set rngData = Range("G2:AE" & lastRow)
v = rngData

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, rngData = v, returns whole processed array to range.

combo

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

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

Thank you again for your help

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

#### Quote:

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,

Just traded in my OLD subtlety...
for a NUance!

"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!

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

(OP)
Thanks SkipVought and understand. Appreciate you posting reply.

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

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:

#### CODE

For I = 2 To lastRow
Select Case Cells(I, "G").Value
Case "IRS"
Select Case Cells(I, "J").Value
Cells(I, "T").Value = "Client"
Cells(I, "U").Value = "Dealer"
Case "SELL"
Cells(I, "T").Value = "Dealer"
Cells(I, "U").Value = "Client"
End Select
Case Else
Select Case Cells(I, "J").Value
Cells(I, "T").Value = "Dealer"
Cells(I, "U").Value = "Client"
Case "SELL"
Cells(I, "T").Value = "Client"
Cells(I, "U").Value = "Dealer"
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

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

>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.

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

#### Quote (combo)

The flow in the big If ... End 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 "Fallback" or "Primary".
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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!