VBA Issue with Searching on a form

J

JayDogUK

New Member
Joined
Feb 11, 2023
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I have created a form because there are 1000's of details that are going to be inputted.
As you can see below i am searching for a PO NUMBER which is in the spreadsheet.
But it has come back saying it can not find a record.
ScreenShot1.png

Below is the VBA Coding in the module1
VBA Code: 
Option Explicit
Public EnableEvents As Boolean

Private Sub cmbSearchColumn_Change()

    If Me.EnableEvents = False Then Exit Sub
    
    If Me.cmbSearchColumn.Value = "All" Then
    
        Call Reset
    Else
    
        Me.txtSearch.Value = ""
        Me.txtSearch.Enabled = True
        Me.cmdSearch.Enabled = True
    
    End If


End Sub


Private Sub cmdDelete_Click()

    Dim Irow As Long

    If selected_list = 0 Then
    
        MsgBox "No row is selected.", vbOKOnly + vbInformation, "Delete"
        Exit Sub
        
    End If
    
    Dim i As VbMsgBoxResult
    
    i = MsgBox("Do you want to delete the selected record?", vbYesNo + vbQuestion, "confirmation")
    
    If i = vbNo Then Exit Sub
    
    Irow = Application.WorksheetFunction.Match(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("PODatabase").Range("A:A"), 0)
    
    ThisWorkbook.Sheets("PODatabase").Rows(Irow).Delete
    
    Call Reset
    
    MsgBox "Selected record has been deleted.", vbOKOnly + vbInformation, "Deleted"


End Sub

Private Sub cmdEdit_Click()

    If selected_list = 0 Then
    
    MsgBox "No Row Selected", vbOKOnly + vbInformation, "Edit"
    
    Exit Sub
    
End If

    Me.txtRowNumber.Value = Application.WorksheetFunction.Match(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("Database").Range("a:A"), 0)
    
    Me.cmbCompany.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 1)
    
    Me.cmbtype.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 2)
    
    Me.txtName.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 3)
    
    Me.txtNHS.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 4)
      
    Me.txtpostcode.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 5)
    
    Me.cmbCCG.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 6)
    
    Me.txtReqNo.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 7)
    
    Me.txtPONO.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 8)
    
    MsgBox "Please Make the required changes and click on 'Save' button to update.", vbOKOnly + vbInformation, "Edit"
    

End Sub

Private Sub cmdFullScreen_Click()

    Call Maximize_Restore

End Sub

Private Sub cmdReset_Click()

    Dim msgValue As VbMsgBoxResult
    
    msgValue = MsgBox("Do you want to reset the form?", vbYesNo + vbInformation, "Comfirmation")
    
    If msgValue = vbNo Then Exit Sub

    Call Reset
    
End Sub

Private Sub cmdSave_Click()
 
    Dim msgValue As VbMsgBoxResult
    
    msgValue = MsgBox("Do you want to save the data?", vbYesNo + vbInformation, "Comfirmation")
    
    If msgValue = vbNo Then Exit Sub

    Call Submit
    Call Reset
    
    
End Sub


Private Sub cmdSearch_Click()

    If Me.txtSearch.Value = "" Then
    
        MsgBox "Please enter the search value.", vbOKOnly + vbInformation, "search"
        Exit Sub
        
        End If

        Call SearchData
        
End Sub


Private Sub UserForm_Initialize()
 
    Call Reset
    
End Sub

Rich (BB code): 
Option Explicit
Public EnableEvents As Boolean

Private Sub cmbSearchColumn_Change()

    If Me.EnableEvents = False Then Exit Sub
    
    If Me.cmbSearchColumn.Value = "All" Then
    
        Call Reset
    Else
    
        Me.txtSearch.Value = ""
        Me.txtSearch.Enabled = True
        Me.cmdSearch.Enabled = True
    
    End If


End Sub


Private Sub cmdDelete_Click()

    Dim Irow As Long

    If selected_list = 0 Then
    
        MsgBox "No row is selected.", vbOKOnly + vbInformation, "Delete"
        Exit Sub
        
    End If
    
    Dim i As VbMsgBoxResult
    
    i = MsgBox("Do you want to delete the selected record?", vbYesNo + vbQuestion, "confirmation")
    
    If i = vbNo Then Exit Sub
    
    Irow = Application.WorksheetFunction.Match(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("PODatabase").Range("A:A"), 0)
    
    ThisWorkbook.Sheets("PODatabase").Rows(Irow).Delete
    
    Call Reset
    
    MsgBox "Selected record has been deleted.", vbOKOnly + vbInformation, "Deleted"


End Sub

Private Sub cmdEdit_Click()

    If selected_list = 0 Then
    
    MsgBox "No Row Selected", vbOKOnly + vbInformation, "Edit"
    
    Exit Sub
    
End If

    Me.txtRowNumber.Value = Application.WorksheetFunction.Match(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("Database").Range("a:A"), 0)
    
    Me.cmbCompany.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 1)
    
    Me.cmbtype.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 2)
    
    Me.txtName.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 3)
    
    Me.txtNHS.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 4)
      
    Me.txtpostcode.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 5)
    
    Me.cmbCCG.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 6)
    
    Me.txtReqNo.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 7)
    
    Me.txtPONO.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 8)
    
    MsgBox "Please Make the required changes and click on 'Save' button to update.", vbOKOnly + vbInformation, "Edit"
     

End Sub

Private Sub cmdFullScreen_Click()

    Call Maximize_Restore

End Sub

Private Sub cmdReset_Click()

    Dim msgValue As VbMsgBoxResult
    
    msgValue = MsgBox("Do you want to reset the form?", vbYesNo + vbInformation, "Comfirmation")
    
    If msgValue = vbNo Then Exit Sub

    Call Reset
    
End Sub

Private Sub cmdSave_Click()
 
    Dim msgValue As VbMsgBoxResult
    
    msgValue = MsgBox("Do you want to save the data?", vbYesNo + vbInformation, "Comfirmation")
    
    If msgValue = vbNo Then Exit Sub

    Call Submit
    Call Reset
    
    
End Sub


Private Sub cmdSearch_Click()

    If Me.txtSearch.Value = "" Then
    
        MsgBox "Please enter the search value.", vbOKOnly + vbInformation, "search"
        Exit Sub
        
        End If

        Call SearchData
        
End Sub


Private Sub UserForm_Initialize()
 
    Call Reset
    
End Sub

If anyone could help me solve this issue I will be truly grateful and also this will form / excel form will help out many children within the healthcare sector.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Hi,

What is the code you are using to search and find your PO number ...???
 
Upvote 0
JayDogUK said:
I have created a form because there are 1000's of details that are going to be inputted.
As you can see below i am searching for a PO NUMBER which is in the spreadsheet.
But it has come back saying it can not find a record.
View attachment 85151
Below is the VBA Coding in the module1
VBA Code: 
Option Explicit
Public EnableEvents As Boolean

Private Sub cmbSearchColumn_Change()

    If Me.EnableEvents = False Then Exit Sub
  
    If Me.cmbSearchColumn.Value = "All" Then
  
        Call Reset
    Else
  
        Me.txtSearch.Value = ""
        Me.txtSearch.Enabled = True
        Me.cmdSearch.Enabled = True
  
    End If


End Sub


Private Sub cmdDelete_Click()

    Dim Irow As Long

    If selected_list = 0 Then
  
        MsgBox "No row is selected.", vbOKOnly + vbInformation, "Delete"
        Exit Sub
      
    End If
  
    Dim i As VbMsgBoxResult
  
    i = MsgBox("Do you want to delete the selected record?", vbYesNo + vbQuestion, "confirmation")
  
    If i = vbNo Then Exit Sub
  
    Irow = Application.WorksheetFunction.Match(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("PODatabase").Range("A:A"), 0)
  
    ThisWorkbook.Sheets("PODatabase").Rows(Irow).Delete
  
    Call Reset
  
    MsgBox "Selected record has been deleted.", vbOKOnly + vbInformation, "Deleted"


End Sub

Private Sub cmdEdit_Click()

    If selected_list = 0 Then
  
    MsgBox "No Row Selected", vbOKOnly + vbInformation, "Edit"
  
    Exit Sub
  
End If

    Me.txtRowNumber.Value = Application.WorksheetFunction.Match(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("Database").Range("a:A"), 0)
  
    Me.cmbCompany.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 1)
  
    Me.cmbtype.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 2)
  
    Me.txtName.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 3)
  
    Me.txtNHS.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 4)
    
    Me.txtpostcode.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 5)
  
    Me.cmbCCG.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 6)
  
    Me.txtReqNo.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 7)
  
    Me.txtPONO.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 8)
  
    MsgBox "Please Make the required changes and click on 'Save' button to update.", vbOKOnly + vbInformation, "Edit"
  

End Sub

Private Sub cmdFullScreen_Click()

    Call Maximize_Restore

End Sub

Private Sub cmdReset_Click()

    Dim msgValue As VbMsgBoxResult
  
    msgValue = MsgBox("Do you want to reset the form?", vbYesNo + vbInformation, "Comfirmation")
  
    If msgValue = vbNo Then Exit Sub

    Call Reset
  
End Sub

Private Sub cmdSave_Click()
 
    Dim msgValue As VbMsgBoxResult
  
    msgValue = MsgBox("Do you want to save the data?", vbYesNo + vbInformation, "Comfirmation")
  
    If msgValue = vbNo Then Exit Sub

    Call Submit
    Call Reset
  
  
End Sub


Private Sub cmdSearch_Click()

    If Me.txtSearch.Value = "" Then
  
        MsgBox "Please enter the search value.", vbOKOnly + vbInformation, "search"
        Exit Sub
      
        End If

        Call SearchData
      
End Sub


Private Sub UserForm_Initialize()
 
    Call Reset
  
End Sub

Rich (BB code): 
Option Explicit
Public EnableEvents As Boolean

Private Sub cmbSearchColumn_Change()

    If Me.EnableEvents = False Then Exit Sub
  
    If Me.cmbSearchColumn.Value = "All" Then
  
        Call Reset
    Else
  
        Me.txtSearch.Value = ""
        Me.txtSearch.Enabled = True
        Me.cmdSearch.Enabled = True
  
    End If


End Sub


Private Sub cmdDelete_Click()

    Dim Irow As Long

    If selected_list = 0 Then
  
        MsgBox "No row is selected.", vbOKOnly + vbInformation, "Delete"
        Exit Sub
      
    End If
  
    Dim i As VbMsgBoxResult
  
    i = MsgBox("Do you want to delete the selected record?", vbYesNo + vbQuestion, "confirmation")
  
    If i = vbNo Then Exit Sub
  
    Irow = Application.WorksheetFunction.Match(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("PODatabase").Range("A:A"), 0)
  
    ThisWorkbook.Sheets("PODatabase").Rows(Irow).Delete
  
    Call Reset
  
    MsgBox "Selected record has been deleted.", vbOKOnly + vbInformation, "Deleted"


End Sub

Private Sub cmdEdit_Click()

    If selected_list = 0 Then
  
    MsgBox "No Row Selected", vbOKOnly + vbInformation, "Edit"
  
    Exit Sub
  
End If

    Me.txtRowNumber.Value = Application.WorksheetFunction.Match(Me.lstDatabase.List(Me.lstDatabase.ListIndex, 0), _
    ThisWorkbook.Sheets("Database").Range("a:A"), 0)
  
    Me.cmbCompany.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 1)
  
    Me.cmbtype.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 2)
  
    Me.txtName.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 3)
  
    Me.txtNHS.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 4)
    
    Me.txtpostcode.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 5)
  
    Me.cmbCCG.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 6)
  
    Me.txtReqNo.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 7)
  
    Me.txtPONO.Value = Me.lstDatabase.List(Me.lstDatabase.ListIndex, 8)
  
    MsgBox "Please Make the required changes and click on 'Save' button to update.", vbOKOnly + vbInformation, "Edit"
   

End Sub

Private Sub cmdFullScreen_Click()

    Call Maximize_Restore

End Sub

Private Sub cmdReset_Click()

    Dim msgValue As VbMsgBoxResult
  
    msgValue = MsgBox("Do you want to reset the form?", vbYesNo + vbInformation, "Comfirmation")
  
    If msgValue = vbNo Then Exit Sub

    Call Reset
  
End Sub

Private Sub cmdSave_Click()
 
    Dim msgValue As VbMsgBoxResult
  
    msgValue = MsgBox("Do you want to save the data?", vbYesNo + vbInformation, "Comfirmation")
  
    If msgValue = vbNo Then Exit Sub

    Call Submit
    Call Reset
  
  
End Sub


Private Sub cmdSearch_Click()

    If Me.txtSearch.Value = "" Then
  
        MsgBox "Please enter the search value.", vbOKOnly + vbInformation, "search"
        Exit Sub
      
        End If

        Call SearchData
      
End Sub


Private Sub UserForm_Initialize()
 
    Call Reset
  
End Sub

If anyone could help me solve this issue I will be truly grateful and also this will form / excel form will help out many children within the healthcare sector
Click to expand...

Hi I have just realised it has submitted the wrong VBA Code for Module1. The frmForm code looks OK..
Please Help...

VBA Code: 
Option Explicit
Public IWidth As Integer
Public IHeight As Integer
Public iLeft As Integer
Public iTop As Integer
Public bState As Boolean

'Module1

Sub Reset()

    Dim Irow As Long
    
    Irow = [counta(Database!A:A)]
    
    With frmForm
    
    '----------------------Part 1 ----------------------------
        .cmbCompany.Clear
        
        .cmbCompany.AddItem "Insulet (Omnipod DASH) 49653"
        .cmbCompany.AddItem "Medtronic 11330"
        .cmbCompany.AddItem "Dana 30963"
        .cmbCompany.AddItem "Dexcom G6 48537"
        .cmbCompany.AddItem "Dexcom G7 48537"
        .cmbCompany.AddItem "Roche 45420"
        .cmbCompany.AddItem "Medtrum 60234"
        .cmbCompany.AddItem "TSlim - Air Liquide 60224"
        .cmbCompany.AddItem "Ypsomed 45425"
        .cmbCompany.AddItem "CamAPs"
        
        .txtName.Value = ""
        
        .cmbtype.Clear
        
        .cmbtype.AddItem "Pump"
        .cmbtype.AddItem "CGM"
        .cmbtype.AddItem "App"
        
        .txtNHS.Value = ""
        .txtpostcode.Value = ""
        
        .txtRowNumber.Value = ""
        
        .cmbCCG.Clear
        
        .cmbCCG.AddItem "Ashford"
        .cmbCCG.AddItem "Canterbury & Coastal"
        .cmbCCG.AddItem "South Kent Coastal"
        .cmbCCG.AddItem "Thanet"
        
        '----------------------Part 2--------------------------
        .txtReqNo.Value = ""
        .txtPONO.Value = ""
        
      Call Add_SearchColumn
        ThisWorkbook.Sheets("Database").AutoFilterMode = False
        ThisWorkbook.Sheets("SearchData").AutoFilterMode = False
        ThisWorkbook.Sheets("SearchData").Cells.Clear


        .lstDatabase.ColumnCount = 10
        .lstDatabase.ColumnHeads = True
 
        .lstDatabase.ColumnWidths = "35,135,70,140,80,80,120,80,80"
        
        If Irow > 1 Then
        
        .lstDatabase.RowSource = "Database!A2:I" & Irow
        Else
        
        .lstDatabase.RowSource = "Database!A2:I2"
        
        End If
        
    End With
    
End Sub

Sub Submit()

    Dim SH As Worksheet
    Dim Irow As Long
    
    Set SH = ThisWorkbook.Sheets("Database")
    
    If frmForm.txtRowNumber.Value = "" Then
    
        Irow = [counta(Database!A:A)] + 1
    Else
    
    Irow = frmForm.txtRowNumber.Value
    
    End If
    
    With SH
    
       .Cells(Irow, 1) = Irow - 1
      
       .Cells(Irow, 2) = frmForm.cmbCompany.Value
      
       .Cells(Irow, 3) = frmForm.cmbtype.Value
      
       .Cells(Irow, 4) = frmForm.txtName.Value
      
       .Cells(Irow, 5) = frmForm.txtNHS.Value
      
       .Cells(Irow, 6) = frmForm.txtpostcode.Value
      
       .Cells(Irow, 7) = frmForm.cmbCCG.Value
      
       .Cells(Irow, 8) = frmForm.txtReqNo.Value
      
       .Cells(Irow, 9) = frmForm.txtPONO.Value
      
       End With

End Sub

Sub Show_Form()

    frmForm.Show

End Sub


Function selected_list() As Long

    Dim i As Long
    
    selected_list = 0
    
    For i = 0 To frmForm.lstDatabase.ListCount - 1
    
        If frmForm.lstDatabase.Selected(i) = True Then
        
            selected_list = i + 1
            Exit For
        End If
        
    Next i
    


End Function

Sub Add_SearchColumn()

    frmForm.EnableEvents = False

    With frmForm.cmbSearchColumn
    
        .Clear
        
        .AddItem "ALL"
        .AddItem "COMPANY"
        .AddItem "EQUIPMENT"
        .AddItem "NAME"
        .AddItem "NHS NUMBER"
        .AddItem "POSTCODE"
        .AddItem "CCG"
        .AddItem "REQ NUMBER"
        .AddItem "PO NUMBER"
        
        .Value = "ALL"
        
   End With
    
    frmForm.EnableEvents = True
    
    frmForm.txtSearch.Value = ""
    frmForm.txtSearch.Enabled = False
    frmForm.cmdSearch.Enabled = False

End Sub

Sub SearchData()

    Application.ScreenUpdating = False
    
    Dim shDatabase As Worksheet ' Database sheet
    Dim shSearchData As Worksheet 'SearchData sheet
    
    Dim iColumn As Integer 'To hold the selected column number in Database sheet
    Dim iDatabaseRow As Long 'To store the last non-blank row number available in Database sheet
    Dim iSearchRow As Long 'To hold the last non-blank row number available in SearachData sheet
    
    Dim sColumn As String 'To store the column selection
    Dim sValue As String 'To hold the search text value
    
    
    Set shDatabase = ThisWorkbook.Sheets("Database")
    Set shSearchData = ThisWorkbook.Sheets("SearchData")
    
    
    iDatabaseRow = ThisWorkbook.Sheets("Database").Range("A" & Application.Rows.Count).End(xlUp).Row
    
    
    sColumn = frmForm.cmbSearchColumn.Value
    
    sValue = frmForm.txtSearch.Value
    
    
    iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:J1"), 0)
    
    'Remove filter from Database worksheet
    
    If shDatabase.FilterMode = True Then
    
        shDatabase.AutoFilterMode = False
    
    End If
    
    If frmForm.cmbSearchColumn.Value = "COMPANY" Then
    
          shDatabase.Range("A1:I1" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue
    
    Else
    
        shDatabase.Range("A1:I" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="*" & sValue & "*"
    
    End If
    
    If Application.WorksheetFunction.Subtotal(3, shDatabase.Range("C:C")) >= 4 Then
    
        'Code to remove the previous data from SearchData worksheet
        
        shSearchData.Cells.Clear
        
        shDatabase.AutoFilter.Range.Copy shSearchData.Range("A1")
        
        Application.CutCopyMode = False
        
        iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
        
        frmForm.lstDatabase.ColumnCount = 10
        
        frmForm.lstDatabase.ColumnWidths = "35,135,120,140,80,80,120,75,75"
        
        If iSearchRow > 1 Then
        
            frmForm.lstDatabase.RowSource = "SearchData!A2:I" & iSearchRow
            
            MsgBox "Records found."
        
        End If
        
        
    Else
    
       MsgBox "No record found."
    
    End If

    shDatabase.AutoFilterMode = False
    Application.ScreenUpdating = True


End Sub

Sub Maximize_Restore()

    If Not bState = True Then
    
        IWidth = frmForm.Width
        IHeight = frmForm.Height
        iTop = frmForm.Top
        iLeft = frmForm.Left
        
        With Application
        
        .WindowState = xlMaximized
        frmForm.Zoom = Int(.Width / frmForm.Width * 100)
        
        frmForm.StartUpPosition = 0
        frmForm.Left = .Left
        frmForm.Top = .Top
        frmForm.Width = .Width
        frmForm.Height = .Height
        
    End With
    
        frmForm.cmdFullScreen.Caption = "Restore"
        bState = True
        
    Else
    
        With Application
        
        .WindowState = xlNormal
        frmForm.Zoom = 100
        frmForm.StartUpPosition = 0
        frmForm.Left = iLeft
        frmForm.Width = IWidth
        frmForm.Height = IHeight
        frmForm.Top = iTop
    
    End With

        frmForm.cmdFullScreen.Caption = "Full Screen"
        
        bState = False

    End If
    

End Sub
 
Upvote 0
Hi James please see the sector in above its called 'Module1

Sorry the code pasted wrong.

:)
 
Upvote 0
Screen Shot of spreadsheet relating to the form. Also below relating is SearchData Tab
ScreenShot2.png
 
Upvote 0
You will not get results since the subtotal is not higher than 4
 
Upvote 0
Thank You..
I have tried changing it to different number but it still comes up with No Records Found.
I also get debug messages from time to time.
Can you tell me what it should look like?

VBA Code: 
  If Application.WorksheetFunction.Subtotal(3, shDatabase.Range("C:C")) >= 4 Then
 
Upvote 0
Hi again,
Do not know it all the code you have posted is what you have created yourself ... or if it is patched from different sources ....

Meanwhile, you could test following ... and look into the worksheet to see the result ...
VBA Code: 
Sub TestSearchData()
    Dim shDatabase As Worksheet      ' Database sheet
    Dim shSearchData As Worksheet    'SearchData sheet
    Dim iColumn As Integer           'To hold the selected column number in Database sheet
    Dim iDatabaseRow As Long         'To store the last non-blank row number available in Database sheet
    Dim iSearchRow As Long           'To hold the last non-blank row number available in SearachData sheet
    Dim sColumn As String            'To store the column selection
    Dim sValue As String             'To hold the search text value
    
    
    Set shDatabase = ThisWorkbook.Sheets("Database")
    Set shSearchData = ThisWorkbook.Sheets("SearchData")
    iDatabaseRow = ThisWorkbook.Sheets("Database").Range("A" & Application.Rows.Count).End(xlUp).Row
    sColumn = frmForm.cmbSearchColumn.Value
    sValue = frmForm.txtSearch.Value
    iColumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:J1"), 0)
    'Remove filter from Database worksheet
    If shDatabase.FilterMode = True Then
        shDatabase.AutoFilterMode = False
    End If
    If frmForm.cmbSearchColumn.Value = "COMPANY" Then
        shDatabase.Range("A1:I1" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:=sValue
    Else
        shDatabase.Range("A1:I" & iDatabaseRow).AutoFilter Field:=iColumn, Criteria1:="*" & sValue & "*"
    End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

TVBurke_18
  • Question Question
Trying to Edit Form, and the data is adding "6" and moving data around.
Replies
5
Views
460
TVBurke_18
TVBurke_18
A
  • Question Question
Ambigious Name Detected: Reset
Replies
6
Views
483
aabrazil1
A
J
  • Question Question
VBA Code Help - Search Form
Replies
1
Views
1K
James006
James006
A
  • Question Question
Run-time error '380' Could not set the Value property. Invalid property value
Replies
7
Views
898
aabrazil1
A
A
  • Question Question
Autofilter code based on username
Replies
1
Views
374
aabrazil1
A

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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