Next and Loop Question

[Guest]

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

 

[Guest]

insert this in your loop(s)


if c = "" then exit for

 

[Guest]

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
....

 

[Guest]

Thanks - they work great!