Search Multiple Excel Worksheets using VBA 1

[JasonEnsor]

Hi,

I have recently found a simple find macro for Excel 2003/2010. The find searches one particular sheet for the input that is recieved via an Inputbox. However the Workbook i am wanting to search has 3 Worksheets. I have done a bit of quick fix to make this work but it does feel like there should be a better way of programming it.

The find is being used to populate a userform with data from a spreadsheet, so that it can be updated. The code i am using is listed below. Hopefully someone can help make this code a little more efficient.

Sub Find_First()
    Dim FindString As String
    Dim Rng As Range
    FindString = InputBox("Enter a Search value")
    If Trim(FindString) <> "" Then
    
        With Sheets("Art & Design").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
                Call UpdateData ' This is the update macro that is called

            Else
                With Sheets("Technology").Range("A:A")
                Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                If Not Rng Is Nothing Then
                    Application.Goto Rng, True
                    Call UpdateData ' This is the update macro that is called

                    Else
                        With Sheets("Humanities").Range("A:A")
                            Set Rng = .Find(What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                    If Not Rng Is Nothing Then
                        Application.Goto Rng, True
                        Call UpdateData ' This is the update macro that is called
                    Else
                        MsgBox "Nothing found"
                        ADTForm.Show 'This is the main form that is used to call the find
                    End If
                
            
        End With
        
        
    End If
    End With
    End If
    End With
    End If
End Sub

Any help would be greatly appreciated.

Many Thanks

Jason

 

[SkipVought]

hi,

Sub Find_First()
    Dim FindString As String, ws As Worksheet
    Dim Rng As Range
    FindString = InputBox("Enter a Search value")
    If Trim(FindString) <> "" Then
        
        For Each ws In Worksheets
            With ws
                Select Case .Name
                    Case "Art & Design", "Technology", "Humanities"
                        With .Range("A:A")
                            Set Rng = .Find( _
                                What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                        End With
                        If Not Rng Is Nothing Then
                            Application.Goto Rng, True
                            Call UpdateData ' This is the update macro that is called
                        End If
                End Select
            End With
        Next
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}