Find not finding

[Martin Wheeler]

XL2000
The code below only works on the active sheet. My app runs through 10
sheets of a workbook so I need this to work on each sheet, if applicable.
Any help would be greatly appreciated.
Ta,
Martin
Public Sub BotW(wks As Worksheet)
Application.ScreenUpdating = False
On Error Resume Next
With wks
A = Application.WorksheetFunction.Min(.Range("Q13:Q18"))
B = Application.WorksheetFunction.Small(.Range("Q13:Q18"), "2")
C = Application.WorksheetFunction.CountIf(.Range("L13:L20"),
"<-.05")
D = Application.WorksheetFunction.CountIf(.Range("Q13:Q18"),
"<-.495")
E = Application.WorksheetFunction.CountIf(.Range("Q13:Q18"),
"<-.495")
F = Application.WorksheetFunction.Small(.Range("Q13:Q18"), "3")
G = Application.WorksheetFunction.CountIf(.Range("P710"),
"<-.245")
H = Application.WorksheetFunction.CountIf(.Range("P1122"),
"<-.295")
I = Application.WorksheetFunction.CountIf(.Range("P722"),
"<-.195")
G = G + H
I = I + H
If .Range("E2").Value < 9 Or .Range("G1").Value < 10000 Or
..Range("K7").Value < 1.7 Then
Exit Sub
Else
If .Range("G1").Value < 50000 And G > 6 Or I > 5 And
..Range("G1").Value >= 50000 Then
Exit Sub
Else
If A = 1 And B = 2 And F = 3 Then
Exit Sub
Else
Set Start = Cells.Find(what:=A, _
After:=.Range("Q12"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)
Set CellK = Start.Offset(0, -6)
Set CellP = Start.Offset(0, -1)
If A <= 4 And C <= 2 And CellK < 40 And CellP < -0.175
Then
.Range("P6").Value = "DoNow"
End If
End If
End If
End If
Application.ScreenUpdating = True
End With
End Sub

 

[Don Guillett]

Assuming that it does work, surround in a for each
for each ws in worksheets
your code
next ws

 

[Martin Wheeler]

Hi Don,
Thanks for the help. I'll give it a go.
Ta,
Martin

 

[Don Guillett]

Let us know if it works for you

 

[Martin Wheeler]

Hi Don,
I'm still having problems. The code is now:-

Public Sub BotW(wks As Worksheet)
Application.ScreenUpdating = False
On Error Resume Next
With wks
For Each wks In Worksheets
'code to do stuff
End If
Next
Application.ScreenUpdating = True
End With
End Sub

Any ideas?
Ta,
Martin

 

[Martin Wheeler]

Hi Don,
I've just noticed that my previous acknowledgement thanking you did not go
through yesterday.
In any event thanks for your help.
Ta,
Martin

 

[Don Guillett]

Again, assuming your original code is good, try

Sub BotW()
Application.ScreenUpdating = False

For Each wks In Worksheets

'code to do stuff

Next
Application.ScreenUpdating = True
End Sub

 

[Martin Wheeler]

Hi Don,
I tried the code below but it will not run. My code uses, I forget the
terminology, but the sub name is
Public Sub BotW(wks As Worksheet)
And all the ranges have the dot in front. I need this as my code runs of
OnTime, getting downloads from the net.
So when I tried the code below, having taken out "with wks" it got rejected.
The code works when I am downloading new data off the net. The analytical
package, which the code below is part of, only kicks in after the last
download. Provided I have the active sheet getting the last download the
code below works fine.
The problem is when I want to look at previous workbooks and run updated
analytical code through them. Then it will only do the code below on the
active sheet.
Any ideas?
Thanks for the help.
Ta,
Martin

 

[Don Guillett]

then try
Sub BotW()
Application.ScreenUpdating = False

For Each wks In Worksheets
with wks
'code to do stuff
end with
Next
Application.ScreenUpdating = True

End Sub