Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Posts
    25

    Unanswered: If Statements in Excel macros

    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??

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  3. #3
    Join Date
    Mar 2005
    Posts
    25
    Didn't quite work....it worked if there was a tab for site allowance. If there wasn't it would cause an error.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  5. #5
    Join Date
    Feb 2006
    Posts
    113
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •