If Statements in Excel macros

mike703 · #1 (**permalink**) 01-31-06, 16:22

Trying to figure out a way (if possible) to insert an IF statement so that Excel will search each tab(worksheet) in a workbook and identify a specific worksheet by it's "tab name". My poorly constructed (and non-working) example is below:

If (Sheets = "Site Allowance") Then Sheets("Site Allowance").Select

The tab of the worksheet is called Site Allowance and does not appear on every workbook so it would be very helpful to have an If statement that would search for this tab, format the worksheet if found; if not, it can move on to the next tab with no errors.

Can anybody help me or let me know if this is not possible??

MikeTheBike · #2 (**permalink**) 02-01-06, 08:25

Hi

Maybe somethinh like this

Code:

Sub EnumerateSheets()
    Const cReqdName As String = "Site Allowance"
    Dim sht As Worksheet
    
    
    For Each sht In Sheets
        If sht.Name = cReqdName Then
            sht.Select
            Exit For
        End If  
    Next sht
    
    If ActiveSheet.Name <> cReqdName Then MsgBox "'" & cReqdName & "' Not Found!", vbExclamation, "Sheet Search"
    
End Sub

MTB

mike703 · #3 (**permalink**) 02-01-06, 14:59

Didn't quite work....it worked if there was a tab for site allowance. If there wasn't it would cause an error.

MikeTheBike · #4 (**permalink**) 02-02-06, 03:32

Hi

I do not understand why you get an error as this code work fine for me.

Code:

Sub EnumerateSheets()
    Dim sht As Worksheet
    Const cReqdName As String = "Site Allowance"
    For Each sht In Sheets
    
        If sht.Name = cReqdName Then
            sht.Select
            Exit For
        End If
    Next sht
    
    If ActiveSheet.Name <> cReqdName Then
        MsgBox "'" & cReqdName & "' not found !", vbExclamation, "Not Found"
    Else
        MsgBox "'" & cReqdName & "' found.", vbInformation, "Found"
    End If
End Sub

If it does not find the required sheet name it doesn't do anything (execept to say so now).

What is the error.

Are you running code assuming it has been found anyway ?

MTB

Fazza · #5 (**permalink**) 02-07-06, 22:48

Mike703,

What you want to achieve is routine and easily doable.

However, rather than inefficiently loop through all the sheets (and there could be hundreds) doing string comparisons, try to do something with the particular sheet name you want and see if it causes an error. And, to format the sheet, do not select it - as this also is inefficient.

Code:

Sub Test()

    Dim wks As Worksheet
    
    On Error Resume Next
    
    Set wks = Worksheets("Site Allowance")
    
    If Err = 0 Then
        With wks
'            .your formatting
        End With
    End If
    
    Err.Clear

    Set wks = Nothing

End Sub

HTH,
Fazza

Internet Services

Web Development

Digital Marketing

Consumer Tech