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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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)
 
Upvote 0
not working

hi i tried this and it does not work, im trying to make the code search 2 columns
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
errors

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

Thanks
[/quote]
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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
Back
Top