Multiple conditions using IF clause

R

rinijg

New Member
Joined
May 13, 2011
Messages
47
Hi, I have an userform with 3 textboxes and 2 combo boxes. in the textboxes the user can type some search criteria. Combo boxes have 2 values, AND, OR. User can type a text in TextBox 1 and select AND or OR from comboobox 1, then type another text in textbox2 and again select AND or OR from combo box 2 and type another text in textbox3. I have to search for these texts in different worksheets of a workbook based on whether the combobox value is and or or. If textbox 1, 2 and 3 are connected by AND, then these three words must be present in the worksheet. If these three words are connected by OR, then atleast one should be present in the worksheet. If combobox 1 is AND and 2 is OR, first 2 words must be present together or 3rd word must be present. This is the idea.

Could someone please help me out in writing a code for this?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
Hi

How many worksheets should it search in? What are the names of the worksheets. What are the ranges involved? Is it one worksheet per textbox (i.e. search the value in TextBox1 in one worksheet, TextBox2 in another worksheet etc.)?
 
Upvote 0
Actually I have written the code. It is given below.

Code: 
Sub searchname()
    Dim DestBook As Workbook, SrcBook As Workbook
    Dim Lost1 As Variant
    Dim Lost2 As Variant
    Dim Lost3 As Variant
    Dim rngFound1 As Range, sh As Worksheet, shOutput As Worksheet
    Dim rngFound2 As Range
    Dim rngFound3 As Range
    Dim combo1 As String
    Dim combo2 As String
 Application.ScreenUpdating = False
Set SrcBook = ThisWorkbook
search_criteria.Show
Lost1 = SrcBook.Worksheets("Sheet4").Cells(1, 3).Value
Lost2 = SrcBook.Worksheets("Sheet4").Cells(2, 3).Value
Lost3 = SrcBook.Worksheets("Sheet4").Cells(3, 3).Value
combo1 = SrcBook.Worksheets("Sheet4").Cells(4, 3).Value
combo2 = SrcBook.Worksheets("Sheet4").Cells(5, 3).Value
SrcBook.Worksheets("Sheet4").Range("C1:C5").ClearContents
'Lost = InputBox(prompt:="Type in the details you are looking for!", _
            '        Title:=" Find what?", Default:="")
         If Lost1 = "" Then Exit Sub
Set DestBook = Workbooks.Open("C:\Documents and Settings\jgr\Desktop\WORKING\Database.xls")
    Set shOutput = SrcBook.Worksheets("Find_Result")
    shOutput.Range("A2:A65536").ClearContents
 
    
   
    
    For Each sh In DestBook.Worksheets
        
   
        
            With sh.UsedRange
                
                Set rngFound1 = .Find(What:=Lost1, LookIn:=xlValues)
                Set rngFound2 = .Find(What:=Lost2, LookIn:=xlValues)
                Set rngFound3 = .Find(What:=Lost3, LookIn:=xlValues)
                
                
                If combo1 = "AND" & combo2 = "AND" Then
                
                If Not rngFound1 Is Nothing Then
                If Not rngFound2 Is Nothing Then
                If Not rngFound3 Is Nothing Then
                    shOutput.Hyperlinks.Add _
        Anchor:=shOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), _
        Address:=DestBook.FullName, _
        SubAddress:="'" & rngFound1.Parent.Name & "'" & "!" & rngFound1.Address, _
        ScreenTip:="Match found in cell: " & rngFound1.Address(0, 0), _
        TextToDisplay:=sh.Name
    End If
     End If
     End If
     
        End If
        
        
            If combo1 = "OR" & combo2 = "OR" Then
            
            If Not rngFound1 Is Nothing Then
               shOutput.Hyperlinks.Add _
        Anchor:=shOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), _
        Address:=DestBook.FullName, _
        SubAddress:="'" & rngFound1.Parent.Name & "'" & "!" & rngFound1.Address, _
        ScreenTip:="Match found in cell: " & rngFound1.Address(0, 0), _
        TextToDisplay:=sh.Name
        
        ElseIf Not rngFound2 Is Nothing Then
        shOutput.Hyperlinks.Add _
        Anchor:=shOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), _
        Address:=DestBook.FullName, _
        SubAddress:="'" & rngFound2.Parent.Name & "'" & "!" & rngFound2.Address, _
        ScreenTip:="Match found in cell: " & rngFound2.Address(0, 0), _
        TextToDisplay:=sh.Name
        
        ElseIf Not rngFound3 Is Nothing Then
         shOutput.Hyperlinks.Add _
        Anchor:=shOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), _
        Address:=DestBook.FullName, _
        SubAddress:="'" & rngFound3.Parent.Name & "'" & "!" & rngFound3.Address, _
        ScreenTip:="Match found in cell: " & rngFound3.Address(0, 0), _
        TextToDisplay:=sh.Name
        End If
        
        End If
        
        
        
        If combo1 = "AND" & combo2 = "OR" Then
        
        If Not rngFound1 Is Nothing Then
        
                If Not rngFound2 Is Nothing Then
                 shOutput.Hyperlinks.Add _
        Anchor:=shOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), _
        Address:=DestBook.FullName, _
        SubAddress:="'" & rngFound1.Parent.Name & "'" & "!" & rngFound1.Address, _
        ScreenTip:="Match found in cell: " & rngFound1.Address(0, 0), _
        TextToDisplay:=sh.Name
        End If
        
        ElseIf Not rngFound3 Is Nothing Then
         shOutput.Hyperlinks.Add _
        Anchor:=shOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), _
        Address:=DestBook.FullName, _
        SubAddress:="'" & rngFound3.Parent.Name & "'" & "!" & rngFound3.Address, _
        ScreenTip:="Match found in cell: " & rngFound3.Address(0, 0), _
        TextToDisplay:=sh.Name
        End If
        
        End If
        
        
        
        If combo2 = "AND" & combo1 = "OR" Then
        
        If Not rngFound1 Is Nothing Then
        shOutput.Hyperlinks.Add _
        Anchor:=shOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), _
        Address:=DestBook.FullName, _
        SubAddress:="'" & rngFound1.Parent.Name & "'" & "!" & rngFound1.Address, _
        ScreenTip:="Match found in cell: " & rngFound1.Address(0, 0), _
        TextToDisplay:=sh.Name
        
        ElseIf Not rngFound2 Is Nothing Then
        
        If Not rngFound3 Is Nothing Then
           shOutput.Hyperlinks.Add _
        Anchor:=shOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), _
        Address:=DestBook.FullName, _
        SubAddress:="'" & rngFound2.Parent.Name & "'" & "!" & rngFound2.Address, _
        ScreenTip:="Match found in cell: " & rngFound2.Address(0, 0), _
        TextToDisplay:=sh.Name
        End If
        
        End If
        
        End If
        
        If combo1 = "" Then
        
        If Not rngFound1 Is Nothing Then
        shOutput.Hyperlinks.Add _
        Anchor:=shOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), _
        Address:=DestBook.FullName, _
        SubAddress:="'" & rngFound1.Parent.Name & "'" & "!" & rngFound1.Address, _
        ScreenTip:="Match found in cell: " & rngFound1.Address(0, 0), _
        TextToDisplay:=sh.Name
        End If
        
        End If
        
        If combo1 = "AND" & combo2 = "" Then
        If Not rngFound1 Is Nothing Then
        
                If Not rngFound2 Is Nothing Then
                 shOutput.Hyperlinks.Add _
        Anchor:=shOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), _
        Address:=DestBook.FullName, _
        SubAddress:="'" & rngFound1.Parent.Name & "'" & "!" & rngFound1.Address, _
        ScreenTip:="Match found in cell: " & rngFound1.Address(0, 0), _
        TextToDisplay:=sh.Name
        End If
        End If
        End If
        If combo1 = "OR" & combo2 = "" Then
        If Not rngFound1 Is Nothing Then
        shOutput.Hyperlinks.Add _
        Anchor:=shOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), _
        Address:=DestBook.FullName, _
        SubAddress:="'" & rngFound1.Parent.Name & "'" & "!" & rngFound1.Address, _
        ScreenTip:="Match found in cell: " & rngFound1.Address(0, 0), _
        TextToDisplay:=sh.Name
        
        ElseIf Not rngFound2 Is Nothing Then
         shOutput.Hyperlinks.Add _
        Anchor:=shOutput.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0), _
        Address:=DestBook.FullName, _
        SubAddress:="'" & rngFound2.Parent.Name & "'" & "!" & rngFound2.Address, _
        ScreenTip:="Match found in cell: " & rngFound2.Address(0, 0), _
        TextToDisplay:=sh.Name
        End If
        
        End If
        
        
           
End With
            
        
    Next
    shOutput.Activate
    shOutput.Range("A1").Select
   DestBook.Close Savechanges:=True
 On Error GoTo 0
Set DestBook = Nothing
Set SrcBook = Nothing
End Sub

I have copied the values from textbox1,2,3 and comboboxes 1 and 2 to sheet4 Range C1 to C5. It is showing type mismatch error for the line
Code: 
If combo1 = "AND" & combo2 = "AND" Then
I presume there is some syntax error. Can you clear this error please?
 
Upvote 0
Well.. I haven't gone through your full code.... but the syntax should be



Code: 
If combo1 = "AND" And combo2 = "AND" Then
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Userform edit multiple textboxes at the same time
Replies
4
Views
106
Formula11
F
S
  • Solved
copy data from userform to multiple sheets based on checkbox selection
Replies
4
Views
189
Starchaser
S
I
  • Solved
Add value into userform Combobox & also on worksheet Table
Replies
4
Views
147
ipbr21054
I
J
  • Question Question
User form successfully ran but no results are showing
Replies
1
Views
311
rlv01
R
J
  • Question Question
Userform inconsistent responses
Replies
1
Views
218
Candyman8019
C

Forum statistics

Threads
1,224,597
Messages
6,179,813
Members
452,945
Latest member
Bib195

Share this page

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