Limit the Loop to 500 Rows

I have a macro that runs well down a column but I need to define the
Do Until
statement to stop it at row 500 and am stumped on how to do this.
Please help with this simple request. Thanks
Steve

If you post the relevant code it would be helpful. But in general:

Dim c As Range
Set c = Range("A1")
Do Until c.Row > 500
Debug.Print c.Row
Set c = c.Offset(1)
Loop

--

Vasant




"SteveF" <[email protected]> wrote in message
news:[email protected]...
>I have a macro that runs well down a column but I need to define the
> Do Until
> statement to stop it at row 500 and am stumped on how to do this.
> Please help with this simple request. Thanks
> Steve

Hi

For i=1 To 500
varValue=ActiveSheet.Range("A" & i).Value
Next i


Arvi Laanemets


"SteveF" <[email protected]> wrote in message
news:[email protected]...
> I have a macro that runs well down a column but I need to define the
> Do Until
> statement to stop it at row 500 and am stumped on how to do this.
> Please help with this simple request. Thanks
> Steve

Here is the code - It still goes to the bottom of the sheet and keeps
trying to run. What have I done wrong. Thanks
Steve
Dim c As Range
Set c = Range("A1")

Range("C1").Select
Selection.End(xlDown).Select
Do Until c.Row > 500
Selection.Insert Shift:=xlToRight
Selection.End(xlDown).Select
Loop
MsgBox ("Finished!")

On Sat, 25 Jun 2005 22:35:17 -0400, "Vasant Nanavati" <vasantn AT aol
DOT com> wrote:

>If you post the relevant code it would be helpful. But in general:
>
>Dim c As Range
>Set c = Range("A1")
>Do Until c.Row > 500
> Debug.Print c.Row
> Set c = c.Offset(1)
>Loop

Hi Steve,

Since you:

> Set c = Range("A1")

c.Row has a fixed value of 1 and the loop ending condition:

> Do Until c.Row > 500

is never met and , thus, you have a continuos loop.

Additionally, your code makes selections which are rarely necessary and
usually inefficient.

The following may do what you want - if not, post back:

Sub Tester()
Dim startCell As Range, endCell As Range
Dim rcell As Range

Set startCell = Range("C1")
Set endCell = startCell.End(xlDown)

For Each rcell In Range(startCell, endCell)
If rcell.Row > 500 Then Exit For
rcell.Insert Shift:=xlToRight
Next

End Sub

---
Regards,
Norman



"SteveF" <[email protected]> wrote in message
news:[email protected]...
> Here is the code - It still goes to the bottom of the sheet and keeps
> trying to run. What have I done wrong. Thanks
> Steve
> Dim c As Range
> Set c = Range("A1")
>
> Range("C1").Select
> Selection.End(xlDown).Select
> Do Until c.Row > 500
> Selection.Insert Shift:=xlToRight
> Selection.End(xlDown).Select
> Loop
> MsgBox ("Finished!")
>
> On Sat, 25 Jun 2005 22:35:17 -0400, "Vasant Nanavati" <vasantn AT aol
> DOT com> wrote:
>
>>If you post the relevant code it would be helpful. But in general:
>>
>>Dim c As Range
>>Set c = Range("A1")
>>Do Until c.Row > 500
>> Debug.Print c.Row
>> Set c = c.Offset(1)
>>Loop
>