Excel Search VBA

kendals

Board Regular
Joined
May 19, 2007
Messages
55
Hi I have the following code below, im not sure how to modify it to make it only search 2 columns of information, how can do this?
thanks
:eek:
Code:
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
change
Code:
Set StrFindWhat = Cells.Find(What:=WhatToFind, LookIn:=xlValues, LookAt _ 
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

to

Code:
Set StrFindWhat = Columns("A:B").Find(What:=WhatToFind, LookIn:=xlValues, LookAt _ 
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
 

kendals

Board Regular
Joined
May 19, 2007
Messages
55
not working

hi i tried this and it does not work, im trying to make the code search 2 columns
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Re: not working

hi i tried this and it does not work, im trying to make the code search 2 columns

Like I asked, you need explain what you want to do in WORDS.
 

kendals

Board Regular
Joined
May 19, 2007
Messages
55
explaination

ok, i have a worksheet with information on, i just need to the search to search column A and B. At the moment the code searches the wholesheet.
I have column A which shows the name of the program and column B that shows the category of the program. How do i tell the script to just look at the A and B columns
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

try
Code:
Sub test()
Dim r As Range, ff As String, mySearch
mySearch = Split(InputBox("Enter word(s) separate by a comma"),",")
If (mySearch = "") + (mySearch = False) Then Exit Sub
Set r = Columns("a").Find(mySearch(0),,,xlPart)
If Not  r Is Nothing Then
     ff = r.Address
     Do
          If UBound(mySearch) > 0 Then
               If r.Offset(,1).Value = mySearch(1) Then
                    If x Is Nothing Then
                         Set x = r.Resize(,2)
                    Else
                         Set x = Union(x, r.Resize(,2))
                    End If
          Else
               If x Is Nothing Then
                    Set x = r
               Else
                    Set x = Union(x, r)
               End If
          End If
          Set r = Columns("a").FindNext(r)
     Loop Until ff = r.Address
     x.Select
Else
     MsgBox "Not found"
End If
End Sub
 

kendals

Board Regular
Joined
May 19, 2007
Messages
55
errors

The code gives error on statement Loop Until ff = r.Address
Run error is Compile error: loop without do.

Thanks
[/quote]
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Ahhh,
try
Code:
Sub test()
Dim r As Range, ff As String, mySearch
mySearch = Split(InputBox("Enter word(s) separate by a comma"),",")
If (mySearch = "") + (mySearch = False) Then Exit Sub
Set r = Columns("a").Find(mySearch(0),,,xlPart)
If Not  r Is Nothing Then
     ff = r.Address
     Do
          If UBound(mySearch) > 0 Then
               If InStr(1, r.Offset(,1).Value,mySearch(1),1) > 0 Then
                    If x Is Nothing Then
                         Set x = r.Resize(,2)
                    Else
                         Set x = Union(x, r.Resize(,2))
                    End If
               End If
          Else
               If x Is Nothing Then
                    Set x = r
               Else
                    Set x = Union(x, r)
               End If
          End If
          Set r = Columns("a").FindNext(r)
     Loop Until ff = r.Address
     If Not x Is Nothing Then
          x.Select
     Else
          MsgBox "Not Found"
     End If
Else
     MsgBox "Not found"
End If
End Sub
 

kendals

Board Regular
Joined
May 19, 2007
Messages
55
runtime error 13 type mismatch

When running the macro and searching, i get runtime error 13 type mismatch and the same happens if you click cancel and dont enter any search info.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top