exiting loop

[Nichole09]

I have been experimenting with loops and am currently stuck. I am hopingsomeone can help me or explain why my code is not working properly. I havetried the below code with for each, do while, do until, ect. I have triedvaries loops and I receive the same result and I am not sure why. The codebelow works as it should except for one item, it does not exit or end the loopwhen the value of the row equals “check”. Each time I have changed the code to experiment if it will work, italways exit’s two rows down from the cell that contains “check”. I am not surewhy. Please help!

Sub Pasting_Conditions()
Dim ws4 As Worksheet
Set ws4 = Worksheets("Practice")
Dim CDstatus As String
Dim xlrange As range, xlrange2 As range
Dim valuetofind As String, valuetofind2 As String
Dim followup As String, statusdate As String
Dim r As range, r2 As range
Dim q As range, q2 As range
Dim i As Variant

Application.EnableEvents = False
 Application.ScreenUpdating = False
 valuetofind = "Prior To Closing(PTC):  "
 Set xlrange = Worksheets("Practice").range("D93:D101")
 
 For Each cell In xlrange
 If cell.value = valuetofind Then
 cell.Activate
 End If
 Next
 
 
 Set r = ws4.range("O70")
 Set q = activecell.Offset(2, 0)
 
 Set r2 = ws4.range("q70")
 Set q2 = activecell.Offset(2, 2)
 
 valuetofind2 = "check"
 Set xlrange2 = Worksheets("Practice").range("D93:D101")
 
 For Each cell In xlrange2
 If cell.value = valuetofind2 Then
 Exit For
 Else
 
 r.value = q.value
 r2.value = q2.value
 Set r = r.Offset(1, 0)
 Set q = q.Offset(1, 0)
 Set r2 = r2.Offset(1, 0)
 Set q2 = q2.Offset(1, 0)
 
  End If
  
Next
  

Application.EnableEvents = True
 Application.ScreenUpdating = True
End Sub

 

[Fluff]

Would that by chance be the cell that contains "Prior To Closing(PTC): "

 

[Nichole09]

Hi Fluff. No the cell value two rows down from "check" varies. I have changed it several times to experiment and its always text. Each time I change the second row down from the row that has“check” the code still continues until two rows down from Check. Not sure howthat is possible.

 

[Fluff]

Without knowing what your data looks like, or what you are trying to do, it's difficult to diagnose.
Can you explain what you are trying to do & post some example data.

 

[Nichole09]

The goal is to organize a list of tasks the user will pasteinto the worksheet. The data the user is pasting is from a website. (it’s a listof status’s) The user will always paste the data in range(“D93:D101”). What I amattempting to do with my code is copy that data and put each status in anothersection of the same sheet starting at range(“O70”).

I wantthe code to stop pasting data when the value its pasting from (D94:D105) = “check”then I want the loop to stop and this data is no longer pasted even thoughthere are more values after check. Yet, my loop continues to put the valuesthat follow “check” and then ends 2 rows down from “check”.

What ishappening when I run this code: The code stops copying values after the secondrow of the cell that = check


Rows: Rows:

D93 = Pending O70 = Pending

D94 = Pending O71 = Pending

D95 = check O72 = check

D96 = complete O73 = complete

D97 = complete O74 =

What should be happening:



Rows: Rows:

D93 = Pending O70 = Pending

D94 = Pending O71 = Pending

D95 = check O72 =

D96 = complete O73 =

D97 = complete O74 =



I want theloop to exit when the next row down it is looking to copy equals check.

 

[Fluff]

Are you sure it's copying the correct values?
This line of code

 Set q = activecell.Offset(2, 0)

is setting q to be 2 cells below where it finds
"Prior To Closing(PTC): "
So if it finds "Prior To Closing(PTC): " in D93 you will copy the value from D95 to O70

 

[Akuini]

I want theloop to exit when the next row down it is looking to copy equals check.

If I understand you correctly, maybe this is what you want.

Sub Pasting_Conditions()

With Worksheets("Practice")
    For i = 93 To 101
        .Cells(i - 23, "D").Value = .Cells(i, "O").Value
        If .Cells(i, "O").Value = "check" Then Exit For
    Next
End With
End Sub

But I don't understand, why do you mention 2 different range: (D93:D101) & (D94:D105)?

The user will always paste the data in range(“D93:D101”)

I wantthe code to stop pasting data when the value its pasting from (D94:D105) = “check”

 

[Nichole09]

Hi Akuini. I am sorry for the delay in my response. This code is just wanted I was looking to accomplish! Thank you!!

 

[Akuini]

Hi Akuini. I am sorry for the delay in my response. This code is just wanted I was looking to accomplish! Thank you!!

Your welcome & thanks for the reply