Next and Loop Question

[Paige]

The following code attempts to fill in (or leave blank) what is in Columns H
and I based upon what is in Column A same row (i.e., whether Column A same
row is either filled in or blank) and what is on a certain cell in another
speadsheet (either 'Yes', 'No', or blank).

I need to modify it so that it doesn't run through all 1000 rows each time,
causing excessive run time; the code is cumbersome. Maybe by stopping at the
first blank cell in Column A (which I don't know how to do)? Can someone
advise how to fix this please?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
For Each c In Range("$H$14:$H$1000")
If c.Offset(0, -7).Value <> "" And Worksheets("Input Tab
#1").Range("C16").Value
= "Yes" Then c.Value = c.Offset(-1, 0)
Next
For Each c In Range("$I$14:$I$1000")
If c.Offset(0, -8).Value <> "" And Worksheets("Input Tab
#1").Range("C17").Value
= "Yes" Then c.Value = c.Offset(-1, 0)
Next

For Each c In Range("$H$14:$H$1000")
If c.Offset(0, -7).Value <> "" And Worksheets("Input Tab
#1").Range("C16").Value
= "No" Then c.Value = c.Offset(-1, 0)
Next
For Each c In Range("$I$14:$I$1000")
If c.Offset(0, -8).Value <> "" And Worksheets("Input Tab
#1").Range("C17").Value
= "No" Then c.Value = c.Offset(-1, 0)
Next

For Each c In Range("$H$14:$H$1000")
If c.Offset(0, -7).Value <> "" And Worksheets("Input Tab
#1").Range("C16").Value
= "" Then c.Value = c.Offset(-1, 0)
Next
For Each c In Range("$I$14:$I$1000")
If c.Offset(0, -8).Value <> "" And Worksheets("Input Tab
#1").Range("C17").Value
= "" Then c.Value = c.Offset(-1, 0)
Next

For Each c In Range("$H$14:$H$1000")
If c.Offset(0, -7).Value = "" Then c.Value = ""
Next
For Each c In Range("$I$14:$I$1000")
If c.Offset(0, -8).Value = "" Then c.Value = ""
Next

End Sub

[ben]

insert this in your loop(s)


if c = "" then exit for
--
When you lose your mind, you free your life.


"Paige" wrote:

> The following code attempts to fill in (or leave blank) what is in Columns H
> and I based upon what is in Column A same row (i.e., whether Column A same
> row is either filled in or blank) and what is on a certain cell in another
> speadsheet (either 'Yes', 'No', or blank).
>
> I need to modify it so that it doesn't run through all 1000 rows each time,
> causing excessive run time; the code is cumbersome. Maybe by stopping at the
> first blank cell in Column A (which I don't know how to do)? Can someone
> advise how to fix this please?
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim c As Range
> For Each c In Range("$H$14:$H$1000")
> If c.Offset(0, -7).Value <> "" And Worksheets("Input Tab
> #1").Range("C16").Value
> = "Yes" Then c.Value = c.Offset(-1, 0)
> Next
> For Each c In Range("$I$14:$I$1000")
> If c.Offset(0, -8).Value <> "" And Worksheets("Input Tab
> #1").Range("C17").Value
> = "Yes" Then c.Value = c.Offset(-1, 0)
> Next
>
> For Each c In Range("$H$14:$H$1000")
> If c.Offset(0, -7).Value <> "" And Worksheets("Input Tab
> #1").Range("C16").Value
> = "No" Then c.Value = c.Offset(-1, 0)
> Next
> For Each c In Range("$I$14:$I$1000")
> If c.Offset(0, -8).Value <> "" And Worksheets("Input Tab
> #1").Range("C17").Value
> = "No" Then c.Value = c.Offset(-1, 0)
> Next
>
> For Each c In Range("$H$14:$H$1000")
> If c.Offset(0, -7).Value <> "" And Worksheets("Input Tab
> #1").Range("C16").Value
> = "" Then c.Value = c.Offset(-1, 0)
> Next
> For Each c In Range("$I$14:$I$1000")
> If c.Offset(0, -8).Value <> "" And Worksheets("Input Tab
> #1").Range("C17").Value
> = "" Then c.Value = c.Offset(-1, 0)
> Next
>
> For Each c In Range("$H$14:$H$1000")
> If c.Offset(0, -7).Value = "" Then c.Value = ""
> Next
> For Each c In Range("$I$14:$I$1000")
> If c.Offset(0, -8).Value = "" Then c.Value = ""
> Next
>
> End Sub

[Jim Thomlinson]

Not knowing your data the first blank cell could be a perfectly acceptable
solution. In general however it is usually better to set your stopping point
at the last populated cell... Give this a try...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
dim rngToSearch as range

with activesheet
set rngToSearch = .range(.range("H14"), .cells(rows.count, "H").end(xlup))
end with

For Each c In rngToSearch
If c.Offset(0, -7).Value <> "" And Worksheets("Input Tab
#1").Range("C16").Value
= "Yes" Then c.Value = c.Offset(-1, 0)
Next
....
--
HTH...

Jim Thomlinson


"Paige" wrote:

> The following code attempts to fill in (or leave blank) what is in Columns H
> and I based upon what is in Column A same row (i.e., whether Column A same
> row is either filled in or blank) and what is on a certain cell in another
> speadsheet (either 'Yes', 'No', or blank).
>
> I need to modify it so that it doesn't run through all 1000 rows each time,
> causing excessive run time; the code is cumbersome. Maybe by stopping at the
> first blank cell in Column A (which I don't know how to do)? Can someone
> advise how to fix this please?
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim c As Range
> For Each c In Range("$H$14:$H$1000")
> If c.Offset(0, -7).Value <> "" And Worksheets("Input Tab
> #1").Range("C16").Value
> = "Yes" Then c.Value = c.Offset(-1, 0)
> Next
> For Each c In Range("$I$14:$I$1000")
> If c.Offset(0, -8).Value <> "" And Worksheets("Input Tab
> #1").Range("C17").Value
> = "Yes" Then c.Value = c.Offset(-1, 0)
> Next
>
> For Each c In Range("$H$14:$H$1000")
> If c.Offset(0, -7).Value <> "" And Worksheets("Input Tab
> #1").Range("C16").Value
> = "No" Then c.Value = c.Offset(-1, 0)
> Next
> For Each c In Range("$I$14:$I$1000")
> If c.Offset(0, -8).Value <> "" And Worksheets("Input Tab
> #1").Range("C17").Value
> = "No" Then c.Value = c.Offset(-1, 0)
> Next
>
> For Each c In Range("$H$14:$H$1000")
> If c.Offset(0, -7).Value <> "" And Worksheets("Input Tab
> #1").Range("C16").Value
> = "" Then c.Value = c.Offset(-1, 0)
> Next
> For Each c In Range("$I$14:$I$1000")
> If c.Offset(0, -8).Value <> "" And Worksheets("Input Tab
> #1").Range("C17").Value
> = "" Then c.Value = c.Offset(-1, 0)
> Next
>
> For Each c In Range("$H$14:$H$1000")
> If c.Offset(0, -7).Value = "" Then c.Value = ""
> Next
> For Each c In Range("$I$14:$I$1000")
> If c.Offset(0, -8).Value = "" Then c.Value = ""
> Next
>
> End Sub

[Paige]

Thanks - they work great!

"Paige" wrote:

> The following code attempts to fill in (or leave blank) what is in Columns H
> and I based upon what is in Column A same row (i.e., whether Column A same
> row is either filled in or blank) and what is on a certain cell in another
> speadsheet (either 'Yes', 'No', or blank).
>
> I need to modify it so that it doesn't run through all 1000 rows each time,
> causing excessive run time; the code is cumbersome. Maybe by stopping at the
> first blank cell in Column A (which I don't know how to do)? Can someone
> advise how to fix this please?
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim c As Range
> For Each c In Range("$H$14:$H$1000")
> If c.Offset(0, -7).Value <> "" And Worksheets("Input Tab
> #1").Range("C16").Value
> = "Yes" Then c.Value = c.Offset(-1, 0)
> Next
> For Each c In Range("$I$14:$I$1000")
> If c.Offset(0, -8).Value <> "" And Worksheets("Input Tab
> #1").Range("C17").Value
> = "Yes" Then c.Value = c.Offset(-1, 0)
> Next
>
> For Each c In Range("$H$14:$H$1000")
> If c.Offset(0, -7).Value <> "" And Worksheets("Input Tab
> #1").Range("C16").Value
> = "No" Then c.Value = c.Offset(-1, 0)
> Next
> For Each c In Range("$I$14:$I$1000")
> If c.Offset(0, -8).Value <> "" And Worksheets("Input Tab
> #1").Range("C17").Value
> = "No" Then c.Value = c.Offset(-1, 0)
> Next
>
> For Each c In Range("$H$14:$H$1000")
> If c.Offset(0, -7).Value <> "" And Worksheets("Input Tab
> #1").Range("C16").Value
> = "" Then c.Value = c.Offset(-1, 0)
> Next
> For Each c In Range("$I$14:$I$1000")
> If c.Offset(0, -8).Value <> "" And Worksheets("Input Tab
> #1").Range("C17").Value
> = "" Then c.Value = c.Offset(-1, 0)
> Next
>
> For Each c In Range("$H$14:$H$1000")
> If c.Offset(0, -7).Value = "" Then c.Value = ""
> Next
> For Each c In Range("$I$14:$I$1000")
> If c.Offset(0, -8).Value = "" Then c.Value = ""
> Next
>
> End Sub