Excel Search column a and b

[kendals]

Hi I have the below code used in my app, the problem I am having is that is searches the whole sheet, how do i limit the search range to just column A and B as all i need to search for is programs names and types.

Private Sub CommandButton3_Click() 
Dim StrFindWhat As Range 
Dim NextCell As Range 
Dim WhatToFind As Variant 

WhatToFind = Application.InputBox("Please enter the Application or Service you want to search for?", "Search", , 500, 80, , , 2) 
If WhatToFind <> "" And Not WhatToFind = False Then 
For Each oSheet In ActiveWorkbook.Worksheets 
oSheet.Activate 
oSheet.[b4].Activate 

Set StrFindWhat = Cells.Find(What:=WhatToFind, LookIn:=xlValues, LookAt _ 
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) 
If Not StrFindWhat Is Nothing Then 
StrFindWhat.Activate 
If MsgBox("Found " & Chr(34) & WhatToFind & Chr(34) & " in " & oSheet.Name & "!" & StrFindWhat.Address, vbOKCancel) = vbCancel Then Exit Sub 
On Error Resume Next 
While (Not NextCell Is Nothing) And (Not NextCell.Address = StrFindWhat.Address) 
Set NextCell = Cells.FindNext(After:=ActiveCell) 
If Not NextCell.Address = StrFindWhat.Address Then 
NextCell.Activate 
If MsgBox("Found " & Chr(34) & WhatToFind & Chr(34) & " in " & oSheet.Name & "!" & NextCell.Address, vbOKCancel) = vbCancel Then Exit Sub 
End If 
Wend 
End If 
Set NextCell = Nothing 
Set StrFindWhat = Nothing 
Next oSheet 
End If 
End Sub

[royUK]

Yoir code is difficult to follow. It is a good idea to use a meaningful Prefix to a varaible, but it should be meaningful. So with a prefix of str, the variable should be a String!

You don't need to activate the sheets.

Try

Dim rFindWhat As Range
Dim rFindWhere As Range
Dim NextCell As Range
Dim WhatToFind As Variant

WhatToFind = Application.InputBox("Please enter the Application or Service you want to search for?", "Search", , 500, 80, , , 2)
If WhatToFind <> "" And Not WhatToFind = False Then
'osheet is not declared
For Each osheet In ActiveWorkbook.Worksheets
With osheet
Set rFindWhere = osheet.Range(Cells(1, 1), Cells(Rows.Count, 2).End(xlUp))
Set rFindWhat = rFindWhere.Find(What:=WhatToFind, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
'continues

[kendals]

thanks, i get an error running it, i get "Error expected end with"
how do i close this end of this script off?

[royUK]

Try this

   Dim oSheet As Worksheet
    Dim rFindWhat As Range
    Dim rFindWhere As Range
    Dim NextCell As Range
    Dim WhatToFind As Variant

    WhatToFind = Application.InputBox("Please enter the Application or Service you want to search for?", "Search", , 500, 80, , , 2)
    If WhatToFind <> "" And Not WhatToFind = False Then
        'osheet is not declared
        For Each oSheet In ActiveWorkbook.Worksheets
            With oSheet
                Set rFindWhere = oSheet.Range(Cells(1, 1), Cells(Rows.Count, 2).End(xlUp))
                Set rFindWhat = rFindWhere.Find(What:=WhatToFind, LookIn:=xlValues, LookAt _
                                                                                    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                If Not rFindWhat Is Nothing Then
                    If MsgBox("Found " & Chr(34) & WhatToFind & Chr(34) & " in " & oSheet.Name & "!" & rFindWhat.Address, vbOKCancel) = vbCancel Then Exit Sub
                    rFindWhat.Activate
                    On Error Resume Next
                    While (Not NextCell Is Nothing) And (Not NextCell.Address = rFindWhat.Address)
                        Set NextCell = Cells.FindNext(After:=ActiveCell)
                        If Not NextCell.Address = rFindWhat.Address Then
                            NextCell.Activate
                            If MsgBox("Found " & Chr(34) & WhatToFind & Chr(34) & " in " & oSheet.Name & "!" & NextCell.Address, vbOKCancel) = vbCancel Then Exit Sub
                        End If
                    Wend
                End If
            End With
        Next oSheet
    End If

    Set NextCell = Nothing
    Set rFindWhat = Nothing

[kendals]

hi, the code works fine thanks.
The only problem is it still looks for items in more than one column if it contains the same word.

For example if you had the word foot in col A, B and C it would being up the result for C as well and any other cols that has this info after that. How do you lock it down to just 2 cols.
regards
Kunal

[royUK]

When the search finds the first instance it informs the user. If the user clicks OK it continues to search. If the user clicks cancel the search finishes & activates the found cell.