View Full Version : Do Until loop vs array
kualjo
08-26-2014, 10:50 AM
Usually, when I have something I need done in a column, such as in the following code that checks for blanks and/or invalid entries, I have always done a Do Until loop, where I tell it to run until there is a blank cell in an adjacent column (which has already been checked for blanks). I've seen code written by others that would do the same thing, but as an array. My way works just fine, and I get the results I want, but I'm curious as to whether doing it as an array would be a better approach. Maybe it's just a matter of personal preference, but since I'm self-taught and therefore not so great with VBA, I always wonder how the real pros would do the same thing.
Range("A2").Select
Do Until ActiveCell.Offset(0, -1).Value = ""
If ActiveCell.Value = "" Then
ActiveCell.Value = "[desired text or value]"
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
What would this code look like if I wrote it to run the column as an array, knowing that some cells could be blank?
Bob Phillips
08-26-2014, 12:10 PM
That cannot work, you start at A2 and then try to offset 1 column to the left, there are no columns to the left.
Assuming you mean columns A & B, then I wouldn't use a loop or a array
Dim rng As Range
Dim lastrow As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng = .Range("B2").Resize(lastrow - 1).SpecialCells(xlCellTypeBlanks)
If Not rng Is Nothing Then rng.Value = "[desired text or value]"
End With
I all depends on your goal
I prefer the use of arrays because you can reduce the amount of interaction with the worksheet and conseuently speeding up your code.
You can use:
sn=sheet1.usedrange.rows(1)
or
sn=sheet1.cells(1).currentregion.rows(1)
or
sn=sheet1.rows(1).specialcells(2)
or
sn=sheet1.cells(1).resize(,sheet1.cells(1,columns.count).end(xltoleft).colu mn)
If you want you change anything in these cells you can change it in the array first.
If ready you can write the array to the resulting range.
Doing so there's no need to use 'application screenupdating =false', or 'application.calculation=xlmanual', etc. because those events will only be triggered once.
kualjo
08-26-2014, 01:50 PM
@xld: My mistake on the column. I had some extraneous code that I deleted out, and for whatever unnecessary reason, edited it from 'B1' to 'A1'. :dunno I think I see what your code does, though, and will give it a try.
@snb: As written, my code runs pretty fast, even where I have a large number of rows. Speed isn't really an issue. I'm not sure how or where I would use your suggestions; can you give an example of what it would look like?
Thanks to you both!
If you ask for an opinion you get an opinion.
If you ask for a consideration you get a consideration.
If you get a suggestion just try the suggestion in your own situation.
Jan Karel Pieterse
08-28-2014, 02:03 AM
What your current code seems to do is find the first row that is empty one column to the left of the selected cell
when doing this with a keyboard you would:
press left arrow key
press control+down arrow
hit down arrow once more
press right arrow key
enter data.
In code this is:
With Range("B2").Offset(0, -1)
If .Offset(1).Value <> "" Then
.End(xlDown).Offset(1, 1).Value = "[Some Value]"
Else
.Offset(0, 1).Value = "[Some Value]"
End If
End With
Bob Phillips
08-28-2014, 02:51 AM
I think he is trying to replace all of the blank cells in column B with a value.
Jan Karel Pieterse
08-28-2014, 04:46 AM
I stand corrected :-)
I would only use an array if after testing, these took too long. All the work on an array happens in memory, not on the worksheet so it can be noticeably faster with lots of cells to work on. You still need to find the terminator Cell, then set the array to the range as defined by the terminator, loop thru the array and correct any "bad" values, then paste the array back into the Range.
Dim Cel As Range
Set Cel = Range("A2")
Do Until Cel.Offset(0, 1).Value = ""
If Cel.Value = "" Then Cel.Value = "[desired text or value]"
Set Cel = Cel.Offset(1)
Loop
Faster, But not as obvious
LastRow = Range("B2").End(xlDown).Row 'First empty cell in "B" vs xld's lastRow = bottom non-empty cell in "B"
With Columns("A")
For r = 2 To LastRow
If .Cells(r) = "" Then .Cells(r) = "[desired text or value]"
Next r
End With
Bob Phillips
08-28-2014, 02:31 PM
Faster, But not as obvious
LastRow = Range("B2").End(xlDown).Row 'First empty cell in "B" vs xld's lastRow = bottom non-empty cell in "B"
With Columns("A")
For r = 2 To LastRow
If .Cells(r) = "" Then .Cells(r) = "[desired text or value]"
Next r
End With
But 10 times slower than mine :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.