Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

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?

Check out our Excel VBA Resources

Re: Creating an if/then loop which terminates 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?


Re: Creating an if/then loop which terminates at a blank cell

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.