Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Next and Loop Question
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 270
Default Next and Loop Question
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
ben ben is offline
external usenet poster
  
Posts: 232
Default Next and Loop Question
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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,119
Default Next and Loop Question
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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 270
Default Next and Loop Question
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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop question aelewis Excel Discussion (Misc queries) 2 October 24th 07 08:12 PM
loop question choice[_2_] Excel Programming 7 October 30th 05 05:41 AM
Loop question Rob Excel Programming 10 September 13th 05 10:50 PM
loop question dabith Excel Programming 6 June 13th 04 05:28 PM
One more loop question Patti[_5_] Excel Programming 11 June 6th 04 07:14 AM


All times are GMT +1. The time now is 10:01 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement