Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 178
Default loop ends unexpectedly before finishing loop

Sub MoveDone()
Dim i As Integer, ii As Integer
Worksheets("CIT291").Activate
For i = LastRow(ActiveSheet) To 2 Step -1
With Range("a" & i)
If UCase(.Value) = "DONE" Then
Rows(i).Cut Sheets("CIT291_History").Rows(LastRow(Worksheets(" CIT291_History")) + 1)
End If
End With
Next i
End Sub

when the true event occurs, the cut/paste functions properly, but then the code ends. I cannot for the life of me figure out why. there is no other code in the module other than the lastrow() function. Here's the lastrow() function I'm using:

Function LastRow(sh As Worksheet)
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

End Function

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default loop ends unexpectedly before finishing loop

hi Matthew Dyer,

Sub MoveDone()
Dim i As Long, Start As Long
With Worksheets("CIT291")
Start = LastRow(Sheets(.Name))
For i = Start To 2 Step -1
If UCase(.Range("A" & i).Value) = "DONE" Then
.Rows(i).Cut
.Rows(Start + 1).Insert , Shift:=xlDown
End If
Next i
End With
End Sub

isabelle

Le 2016-09-22 Ã* 20:01, Matthew Dyer a écrit :
Sub MoveDone() Dim i As Integer, ii As Integer Worksheets("CIT291").Activate
For i = LastRow(ActiveSheet) To 2 Step -1 With Range("a" & i) If
UCase(.Value) = "DONE" Then Rows(i).Cut
Sheets("CIT291_History").Rows(LastRow(Worksheets(" CIT291_History")) + 1) End
If End With Next i End Sub

when the true event occurs, the cut/paste functions properly, but then the
code ends. I cannot for the life of me figure out why. there is no other code
in the module other than the lastrow() function. Here's the lastrow()
function I'm using:

Function LastRow(sh As Worksheet) LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _
MatchCase:=False).Row

End Function

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default loop ends unexpectedly before finishing loop

oops sorry, please corrected "CIT291" with "CIT291_History"

isabelle

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 178
Default loop ends unexpectedly before finishing loop

On Thursday, September 22, 2016 at 6:22:24 PM UTC-7, isabelle wrote:
oops sorry, please corrected "CIT291" with "CIT291_History"

isabelle


made adjustments so the cut/paste operates properly, but still the code ENDS after the insert command and I CANNOT FIGURE OUT WHY

Sub MoveDone()
Dim i As Long, ii As Long, Start As Long
ii = LastRow(Worksheets("CIT291_History"))
With Worksheets("CIT291")
Start = LastRow(Sheets(.Name))
For i = Start To 2 Step -1
If UCase(.Range("A" & i).Value) = "DONE" Then
.Rows(i).Cut
Worksheets("cit291_history").Rows(ii + 1).Insert , Shift:=xlDown
ii = ii + 1
End If
Next i
End With
End Sub
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default loop ends unexpectedly before finishing loop

Hi,

Am Fri, 23 Sep 2016 09:13:45 -0700 (PDT) schrieb Matthew Dyer:

made adjustments so the cut/paste operates properly, but still the code ENDS after the insert command and I CANNOT FIGURE OUT WHY


try:

Sub MoveDone()
Dim i As Long, LRow As Long

With Worksheets("CIT291")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LRow To 2 Step -1
If UCase(.Cells(i, 1).Value) = "DONE" Then
.Rows(i).Copy _
Sheets("CIT291_History").Cells(Rows.Count, 1).End(xlUp)(2)
.Rows(i).Delete
End If
Next i
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 178
Default loop ends unexpectedly before finishing loop

this is so bizzare. I've tried the .copy/.paste/.delete, I've tried the .cut/.insert, both attempts have the code stop executing after the destination (paste/etc) runs...

On Friday, September 23, 2016 at 9:25:22 AM UTC-7, Claus Busch wrote:
Hi,

Am Fri, 23 Sep 2016 09:13:45 -0700 (PDT) schrieb Matthew Dyer:

made adjustments so the cut/paste operates properly, but still the code ENDS after the insert command and I CANNOT FIGURE OUT WHY


try:

Sub MoveDone()
Dim i As Long, LRow As Long

With Worksheets("CIT291")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LRow To 2 Step -1
If UCase(.Cells(i, 1).Value) = "DONE" Then
.Rows(i).Copy _
Sheets("CIT291_History").Cells(Rows.Count, 1).End(xlUp)(2)
.Rows(i).Delete
End If
Next i
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016


  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default loop ends unexpectedly before finishing loop

Hi,

Am Fri, 23 Sep 2016 09:53:47 -0700 (PDT) schrieb Matthew Dyer:

this is so bizzare. I've tried the .copy/.paste/.delete, I've tried the .cut/.insert, both attempts have the code stop executing after the destination (paste/etc) runs...


your function returns always 1 because your looking for anything with
"*". Your have to search for "". Change the function to:

Function LastRow(sh As Worksheet)
LastRow = sh.Range("A:A").Find(What:="", _
After:=sh.Range("A1"), LookIn:=xlValues).Row

End Function


Regards
Claus B.
--
Windows10
Office 2016
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 178
Default loop ends unexpectedly before finishing loop

Claus,
Totally appreciate your help. That lastrow function wasn't the issue ("*" is the wildcard to look for anything [value, formula, etc]) and I've been using it as is for years on a variety of projects.

One thing I didn't check is the data that I was using. I was working off a workbook someone else sent to me and didn't realize the data was in table form and likewise had other formatting issues that made everything go kablooey. After copy/pasting all of the data into a completely virgin workbook, the codes (all of the versions submitted) works flawlessly.

Thank you again for all of your guys' help!

On Friday, September 23, 2016 at 10:08:23 AM UTC-7, Claus Busch wrote:
Hi,

Am Fri, 23 Sep 2016 09:53:47 -0700 (PDT) schrieb Matthew Dyer:

this is so bizzare. I've tried the .copy/.paste/.delete, I've tried the .cut/.insert, both attempts have the code stop executing after the destination (paste/etc) runs...


your function returns always 1 because your looking for anything with
"*". Your have to search for "". Change the function to:

Function LastRow(sh As Worksheet)
LastRow = sh.Range("A:A").Find(What:="", _
After:=sh.Range("A1"), LookIn:=xlValues).Row

End Function


Regards
Claus B.
--
Windows10
Office 2016


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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 never ends NooK[_31_] Excel Programming 0 July 1st 04 08:46 AM
Loop never ends Bernie Deitrick Excel Programming 0 June 30th 04 07:00 PM
Loop never ends Bob Phillips[_6_] Excel Programming 0 June 30th 04 06:55 PM
Loop never ends Anson[_2_] Excel Programming 0 June 30th 04 06:08 PM
Loop ends early BrianB Excel Programming 0 August 15th 03 12:13 PM


All times are GMT +1. The time now is 07:48 PM.

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

About Us

"It's about Microsoft Excel"