FIND Instead of Loop

[Lukums]

Greetings,

I'm trying to avoid my LOOP which loops every 7 seconds for 5 days 24/7. Some weeks no worries. Other weeks nightmares.

What I'm trying to do is quite straight forward but I have no idea other than calling each sub on a continuous loop.
What I'd love is this instead of Looping over and over I've been told to FIND the value instead of loop.

Honestly I have NO idea how FIND works so an answer with an explanation would be greatly appreciated! As I always thought loops had to be used to find constant values which move/get changed rows moved/deleted etc.

SUB stoplooping()

IF Range("K3").value is "1" THEN

'DOXYZ (huge code here)

end if

Call nextloop

end sub

sub nextloop()

If range("C3").value is "1" THEN

'DOXYZ (huge code here)

end if

'''call nextloop2 etc etc etc.

end sub

 

[My Aswer Is This]

You said:

I'm trying to avoid my LOOP which loops every 7 seconds for 5 days 24/7.

for 5 days
24/7

24/7 normally means 24 hours a day 7 days a week. Not 5 days.

So are you saying you have your computer on 24 hours a day 7 days a week running this same script?


You would need to explain more about what you want the script to do.

Like what do you want the script to do when it find's a value.

And I have never heard of any script that needs to run 24 Hrs. a day 7 day's a week.
​

 

[Lukums]

You said:

I'm trying to avoid my LOOP which loops every 7 seconds for 5 days 24/7.

for 5 days
24/7

24/7 normally means 24 hours a day 7 days a week. Not 5 days.

So are you saying you have your computer on 24 hours a day 7 days a week running this same script?


You would need to explain more about what you want the script to do.

Like what do you want the script to do when it find's a value.

And I have never heard of any script that needs to run 24 Hrs. a day 7 day's a week.
​

Ok so 5 days (leaves running over the weekend) - 7 days.

Ultimately it loops looking for a value 1 on each of our roll forming machines (automation).
When it finds a 1, it grabs the row and shifts it to another spreadsheet.
Saves/Closes
Then goes back to the original spreadsheet loads the next job in and continues at this time the value would be changed back to 0 indicating the job is NOT complete hence not moving the row.
It constantly goes 1/0 and then does XYZ as mentioned above.

We have day/night shift and yes, I suppose you can stop and start between shifts BUT the data is sensitive and ultimately is avoided to tempered with as much as possible.

---

The script does everything perfectly - I'm just seeing if I physically need to Call next - Call next - Call next up to 20 different Subroutines then Pause for 8 seconds and start this looking/shifting process again.

It's very vague I agree, but I wanted to see if I can "hit play" and FIND values as they happen or is FIND a once only then DO something?

 

[My Aswer Is This]

Here is a simple little script I use to search for the value 1
In column A of the active sheet.
The script will enter Yes in column C same row if it finds the value.
But I'm sure if you already know how to keep a script going all the day long this may not help you.

I actually would like to know how you keep a script running 24 Hrs. a day for 5 days.
You would need to modify this to do what you want.
But I do not know how to keep the script running 24 Hrs. a day 5 days a week.

Sub My_Find()
'Modified  9/27/2018  11:29:07 PM  EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = 1
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set SearchRange = Range("A2:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Offset(0, 2).Value = "Yes"
End Sub

 

[Lukums]

Here is a simple little script I use to search for the value 1
In column A of the active sheet.
The script will enter Yes in column C same row if it finds the value.
But I'm sure if you already know how to keep a script going all the day long this may not help you.

I actually would like to know how you keep a script running 24 Hrs. a day for 5 days.
You would need to modify this to do what you want.
But I do not know how to keep the script running 24 Hrs. a day 5 days a week.

Sub My_Find()
'Modified  9/27/2018  11:29:07 PM  EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = 1
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set SearchRange = Range("A2:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Offset(0, 2).Value = "Yes"
End Sub

Thanks matey I'll have a play -

When you say like to know like exactly how?

Happy to place my code here for you to review run your eyes over in an hour or so.
It basically just loops throughout 20 machines (20 different subs) and does various things. Once it reviews all 20, it does a timer control pause for 8 seconds and then re-searches for the values constantly.