PDA

View Full Version : Solved: Looping



kbsudhir
09-16-2008, 05:07 AM
Hi All

I am able to use a loop until to do a set of instructions until a blank cell is found in the column.

Ex.

cnt = 2
do until Range("A"&cnt) = ""
msgbox hello
loop

But my problem is do the same in a row.

Ex.

I want execute a set of instructions till i find a blank cell in a row.
I want to change the column of the row i.e A to B to C to D like that.

Please Guide.

Thanks
Sudhir

MaximS
09-16-2008, 05:19 AM
without looping you can find out the last used column
use following code:


Dim LastColumn As Long
LastColumn = Range("IV1").End(xlToLeft).Column


or if you want to check each cell in a column before jumping to another column till you will find empty cell:


' j is a column number
For j = 1 To 256
' i is a row number
For i = 1 To 65536

If Cells(i, j) = "" Then
Exit Sub
Else

End If

Next i
Next j


or if you want to check each cell in a row before jumping to another row till you will find empty cell:



' i is a row number
For i = 1 To 65536

' j is a column number
For j = 1 To 256

If Cells(i, j) = "" Then
Exit Sub
Else

End If

Next j
Next i

kbsudhir
09-16-2008, 05:22 AM
Thanks MaximS

My mistake I do not wrote my problem clearly.

But I need to pull the data of each & every cell of the row till i reach the blank cell.

Hence increment from one column to another in the row is important.

Kenneth Hobs
09-16-2008, 05:31 AM
If all of the cells are not empty then you will get an infinite loop.

Typically, one iterates using For Each for a range. Methods like Find can be used to find the first blank cell.

This first is a modification of your code for column A. The second is for row 2.

One method is to use Cells. As in Test2. It starts at B2.
Sub test()
Dim cnt As Long
cnt = 2
Do Until Range("A" & cnt) = ""
MsgBox Range("A" & cnt).Address & vbCrLf & Range("A" & cnt).Value
cnt = cnt + 1
Loop
End Sub


Sub test2()
Dim cnt As Long
cnt = 2
Do Until Cells(2, cnt) = ""
MsgBox Cells(2, cnt).Address & vbCrLf & Cells(2, cnt).Value
cnt = cnt + 1
Loop
End Sub

kbsudhir
09-16-2008, 05:48 AM
Thanks MaximS & Kenneth for your help.

:yes :yes