-
Solved: Looping
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.
[VBA]cnt = 2
do until Range("A"&cnt) = ""
msgbox hello
loop[/VBA]
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
-
without looping you can find out the last used column
use following code:
[vba]
Dim LastColumn As Long
LastColumn = Range("IV1").End(xlToLeft).Column
[/vba]
or if you want to check each cell in a column before jumping to another column till you will find empty cell:
[vba]
' 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
[/vba]
or if you want to check each cell in a row before jumping to another row till you will find empty cell:
[vba]
' 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
[/vba]
Last edited by MaximS; 09-16-2008 at 05:34 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.
-
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.
[VBA]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[/VBA]
-
Thanks MaximS & Kenneth for your help.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules