Loop to stop when a text appears in the sheet

[fari1]

i've a loop code, that stops after the last used cell, i want to modify the code to fit in my scenerio, the thing is that i've a web query that brings in data for a range of data in my column A sheet2, in that column i've no.100,200,300 and so on which trigger my worksheet change in sheet1 and bring in web pages, the only source of stopping that loop is when a page is brought in by web query in my sheet1 column A,which says

<h1>No matching filings.<h1>

and this line can be in any row of column A, and i want the loop to stop whenever this line is is shown in the sheet1 column A.

my loop code is

Sub loopA()
Dim cel As Range
Dim mySheet As Worksheet
 
Set mySheet = Sheets("Wquery")
For Each cel In Sheets("data").Range("A" & Sheets("data").Range("A1").End(xlDown).Row & ":A" & Sheets("data").Range("A" & Rows.Count).End(xlUp).Row)
mySheet.Range("A1") = cel.Value
Next cel

End Sub
</PRE>

</PRE>

 

[kpark91]

Use "GoTo" to break out of loop.

While cond1
If cel.Value <> "" then
    GoTo breakout
End If
Loop
 
breakout:

 

[fari1]

hi, sorry being late, wil u plz more elaborate it, i dun get what r u trying to say, since the web page that is brought in the sheetq,conatins that text string and in your code, i dun find anything like this, instead you'r defining blank, while there's no blank

 

[kpark91]

I don't get what you're saying and your phrases are hard to read.

I do not know what you want except that you want to stop the loop under certain condition, which I have already shown in my previous post.

If you want something different, try rewording your question to clearly state what you are looping through (whether it be HTML, bbCode), what you want instead of slapping some code into your question.

If you have trouble understanding the code,
GOTO statement lets the computer skip the next code to specified area of the code.

 

[Rick Rothstein]

I think this modification to your code is what you are asking for...

Sub loopA()
  Dim cel As Range
  Dim mySheet As Worksheet
  Set mySheet = Sheets("Wquery")
  For Each cel In Sheets("data").Range("A" & Sheets("data").Range("A1").End(xlDown).Row & ":A" & Sheets("data").Range("A" & Rows.Count).End(xlUp).Row)
    If cell.Value = "No matching filings." Then Exit For
    mySheet.Range("A1") = cel.Value
  Next cel
End Sub

You can include the "hl" <HL>tags in the String value being tested if you want or need them... I left them out because they made the code listing look "funny".

 

[fari1]

okay i'm rephrasing what i want:

i've two sheets, one is sheet("Wquery") and the other is ("Data").
in each cell of the data sheet Column A,there'r some numbers A1 has 100 A2 has 200 and so on.
in my Wquery, i've a worksheet change, which brings in the web page based upon the cell A1 value of Wquery sheet, and the cell A1 of Wquery gets the value from the data sheet.,i mean 100 and then and then 300 and so on.
my problem is there's no stopping of this web query, until it brings in the page in sheet wquery, in that sheet column A any cell contains the text no filings found. and i want the loop to stop when that such a web page comes that contain such a word.

hope u understood now

 

[fari1]

oh Hi rick, i didn't see your post, i guess all of u are assuming that no filing found is in that range, which is triggering the loop, u've given me another thought, to exit the loop this way, okay then lets say, i want to exit the loop when there's the word no filings found in the range A, but in that case, i've to use the formula, will that effect my loop?

 

[fari1]

what so funny about those tags?
i dun want find any fun thing in them

 

[Rick Rothstein]

oh Hi rick, i didn't see your post, i guess all of u are assuming that no filing found is in that range, which is triggering the loop, u've given me another thought, to exit the loop this way, okay then lets say, i want to exit the loop when there's the word no filings found in the range A, but in that case, i've to use the formula, will that effect my loop?

Let me try one more time to see if I can give you what you originally asked for. Try this modification of your originally posted code...

Sub loopA()
  Dim cel As Range, WQ As Range
  Dim mySheet As Worksheet
  Set mySheet = Sheets("Wquery")
  For Each cel In Sheets("data").Range("A" & Sheets("data").Range("A1").End(xlDown).Row & ":A" & Sheets("data").Range("A" & Rows.Count).End(xlUp).Row)
    mySheet.Range("A1") = cel.Value
[COLOR=darkred]   Set WQ = mySheet.Columns("A").Find("No matching filings.", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)[/COLOR]
[COLOR=darkred]   If WQ Is Nothing Then Exit For[/COLOR]
  Next cel
End Sub

 

[kpark91]

Rick wiill help you with your problem.
I'm done here.