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>

 

[fari1]

why is he getting so angry, anyways, let me try the code

 

[fari1]

its quite a big loop,and my system is slow as well, its quiet late night here, so i'll telling u about it tomorrow, plz dont unsubscribe it, i'll let u know tomorrow

 

[kpark91]

why is he getting so angry, anyways, let me try the code

I'm not??
I was just saying I'm done here because
1) I did not specificallly know what you had in the first place
2) I wanted to let you know that I am no longer working on this question so that you don't need to wait for my replies which may not come in the future.

Anyways, if your code is slow, the big reason may be because you're looping through objects, which takes up much more memory than simple long datatypes (such as bg color, borders and such..)

For example

Option Explicit
Option Base 0
Sub F_O()
Dim LR&, i&
LR = Sheets("data").Range("A" & Rows.count).End(xlUp).Row
For i = 1 To LR
    Range("A" & i).Value = "blarg"
Next i
End Sub

 

[fari1]

thanks for your code, but i need my loop code, rick your code just stops after first time run, i mean loop just take one value out of that range and then stops without going through entire RANGE a

 

[Rick Rothstein]

rick your code just stops after first time run, i mean loop just take one value out of that range and then stops without going through entire RANGE

Whoops, I left out the Not operator from what I meant to post. See if this does what you want...

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 [B][SIZE=3]Not[/SIZE][/B] WQ Is Nothing Then Exit For[/COLOR]
  Next cel
End Sub

 

[fari1]

it is not stoppping rick,just completing the whole A range from data sheet, without taking into account no matching filings in Wquery column A

 

[fari1]

let me just check one thing, then i tell u, where is exactly the problem

 

[Rick Rothstein]

it is not stoppping rick,just completing the whole A range from data sheet, without taking into account no matching filings in Wquery column A

I think at this point, I would have to see your actual workbook so I can test my code directly in order to see what is happing with it as it runs. Are you willing to post it to one of the free file-sharing websites on the Internet? If so (this would be better as other volunteers could try to help you as well), see instructions below. If not, are you willing to send it directly to me? If so, my email address is rickDOTnewsATverizonDOTnet (replace the upper case letters with the symbols they spell out).

You can post it online using one of these free posting websites...

Box: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

Then post the URL they give you for the file back here.

 

[fari1]

oh yes, it did work, your code was missing those html tage which seem funny to you i just thought about them, and added and it worked.

you again rocked Rick, you are amazing, thanks alot

 

[Rick Rothstein]

oh yes, it did work, your code was missing those html tage which seem funny to you i just thought about them, and added and it worked.

you again rocked Rick, you are amazing, thanks alot

Great, I'm glad it worked for you. The reason I didn't include the tags was this forum's comment processor made everything between them gigantic when I previewed the text I had typed.