macro help

[bluefeather8989]

I have 7 books named 101,201,301,401,501,601,901. In each sheet i have the same this list from T3 to U8
101 9
201 9
301 0
401 0
501 0
601 0

(Column U will change from a 9 or a 0 from a formula.)
I need a macro that will find the first 0 and then go to that sheet. So in other words from the list up above the macro would go to sheet 301. now if all are 9 then go to sheet 901.If there is another way do do this plz let me know.

 

[Marcelo Branco]

Hi,

Try the macro below

Sub test()
    Dim shName As String
    
    Sheets("101").Activate
    If Evaluate("=COUNTIF(U3:U8,9)") = 6 Then
        shName = 901
    Else
        On Error Resume Next
        shName = Evaluate("=Index(T3:T8,MATCH(0,U3:U8,0),1)")
        If Err <> 0 Then MsgBox "Not found": Exit Sub
    End If
    Sheets(shName).Activate
End Sub

HTH

M.

 

[Peter_SSs]

My suggestion:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Go_To_Sheet()<br>    <SPAN style="color:#00007F">Dim</SPAN> Found0 <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Range("U3:U8")<br>        <SPAN style="color:#00007F">Set</SPAN> Found0 = .Find(What:=0, After:=.Cells(.Rows.Count), _<br>            LookIn:=xlValues, LookAt:=xlWhole, _<br>            SearchDirection:=xlNext, SearchFormat:=False)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Found0 <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Sheets("901").Activate<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Sheets(Found0.Offset(, -1).Text).Activate<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[bluefeather8989]

My suggestion:


Sub Go_To_Sheet()
Dim Found0 As Range

With Range("U3:U8")
Set Found0 = .Find(What:=0, After:=.Cells(.Rows.Count), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchDirection:=xlNext, SearchFormat:=False)
End With
If Found0 Is Nothing Then
Sheets("901").Activate
Else
Sheets(Found0.Offset(, -1).Text).Activate
End If
End Sub

So sry for getting back so late, been real busy. BTW this code Works great had to change the 0's to 1's. i like to hide my 0's. Find cant see hidden 0's
great work have another one on me