Creating an if/then loop which terminates at a blank cell
Posted by Kristian Fern on February 15, 2002 9:51 AM
I want to create an if/then loop that copies each cells contents in a given column to a cell which is used as a key for vlookups. I would start at the beginning of the column and progress down once cell at at time until the macro hits a Blank cell. How do I get the macro to step down one cell at a time and how do I get it to stop at a blank cell?
Posted by Bob Umlas on February 15, 2002 10:25 AM
Both of these examples are using column A:
For each x in range("A1",Range("A1").end(xldown))
if x = "" then exit for
'work on this cell
Next
OR
For i=1 to 65536
if len(cells(i,1).value)=0 then exit for
'work on cells(i,1)
Next
However, depending on what you really want to do, this can be quite inefficient. It MIGHT be able to be done in one chunk rather than looping at all -- why not post whay you're trying to do?
Posted by Mark O'Brien on February 15, 2002 10:26 AM
Change "FirstCell" to be the address of the cell in the column you want to start with.
Change "FirstTargetCell" to be the cell that you want to be the first cell the data is to be copied to.
Dim FirstCell As Range
Dim FirstTargetCell As Range
Dim i As Integer
Set FirstCell = Sheets("Sheet1").Range("A1")
Set FirstTargetCell = Sheets("Sheet1").Range("B1")
i = 0
Do
FirstTargetCell.Offset(i, 0).Value = FirstCell.Offset(i, 0).Value
i = i + 1
Loop Until FirstCell.Offset(i, 0).Value = ""
