Question About Loops Efficiency

[Sthrncali]

I have a large spreadsheet with 15 columns of data and approx. 95000 rows of entries...

I have a loop that cycles through Columns 2 through 15 for each row and inserts data based on a DGET() function. I was curious if there is an performance difference between:

For r = 2 to ...
For c = 2 to ...

OR

For c = 2 to ...
For r = 2 to ...

I am trying to understand more about how loops work and wondered if there could potentially be a performance difference one way or the other, and under what circumstances would there be a performance difference..

Or since either way is dealing with the same number of cells, does it not matter what direction you loop through?

 

[Colin Legg]

No, they're both the same. I suppose if there was a condition which meant that the looping should be exited and you expected that that condition would be found more quickly by looping down a row (or across a column) then you'll see a benefit doing it one way over the other.

Often you'll find that you can put formulas or values into a block of cells without looping: this will be a lot quicker than the nested loop.

 

[Sthrncali]

Is there anyway you could give me an example of a scenario where you could write to a block of cells at once?

For example, on my spreadsheet, each cell is being filled by either a VLOOKUP or DGET style function...

The value we are looking up is located in ColA, and the range we are looking up is located in Row 1..

So the below is a simplified illustration of how we are filling the cells currently:

For r = 2 to ...
For c = 2 to ...
ws.cells(r,c).value = Application.WorksheetFunction.Vlookup(ws.cells(r,1), myRange, ws.cells(1,c), False)
End Sub

The actual code is not quite this simplistic, but the jist is we are using a value from Col 1 and Row 1 to find the value for each cell in the range...

Would it be possile to fill Col 2 at once in this type of situation, instead of having to loop through every row for Col 2?

 

[JackDanIce]

I think it would make things clearer if you posted your code or at least some version of it. However, instead of looping, you could use FILLDOWN to fill in the values in a single column. Something like:

Dim LastRow as Long
For C = 2 to x 'x being your last column
  LastRow = Cells(Rows.Count, C).End(xlUp).Row
  With Range(Cells(2,C), Cells(LastRow,C))
    .Formula = "=VLOOKUP($A2," & myRange & "," & Cells(1,C) & ",0")
    .FillDown
  End With
Next C

You may need to play around with this, but I think using FillDown will save you a lot of time..

 

[Sthrncali]

Certainly... Here is a sample code:

Dim Home as Worksheet
Dim LookupValue as String
Dim myRange as Range
Dim Col as Integer
Dim Destination as String
 
Set Home = Activesheet
Col = 6
 
For r = 5 To 97
LookupValue = Home.Cells(r, 3).Value
If LookupValue <> "" Then
For c = 5 To 18
Destination = "'" & Home.Cells(4, c).Text & "'" & "!A:Z"
Set myRange = Range(Destination)
Home.Cells(r, c).Value = GetResults(LookupValue, myRange, Col)
End If
Next
Next

GetResults is just a custom VLOOKUP function so I don't have to type out application.worksheetfunction.vlookup() everytime.

The worksheet name that I want to VLOOKUP on is located in row 4 of each column I am filling with data. So during the loop its get the value I want to lookup from Current Row.Column 3, and the Range I want to lookup in from Row 4.Current Column, then it does the Vlookup and fills the cell with a value, then moves to the next column.