Hi, please could anyone help add to the following code so that it will search depending on which textbox, out of three search options is chosen to define the search term. Basically, I'd like to get it to search column A of the worksheet for "pID", column B for "p.code" or column C for "p.title". Any help greatly appreciated.
Code
- Private Sub cmbFind_Click()
- Set ws = Worksheets("Project Proposals")
- Set MyData = ws.Range("a2").CurrentRegion
- Dim strFind As String 'what to find
- Dim FirstAddress As String
- Dim f As Integer
- strFind = Me.pIDs.Value 'what to look for
- With MyData
- .AutoFilter
- Set c = .Find(strFind, LookIn:=xlValues)
- If Not c Is Nothing Then 'found it
- With Me 'load entry to form
- .pID.Value = c.Offset(0, 0).Value
- .pcode.Value = c.Offset(0, 1).Value
- .ptitle.Value = c.Offset(0, 2).Value
- .pdescription.Value = c.Offset(0, 3).Value
- .ptheme.Value = c.Offset(0, 4).Value
- .plead.Value = c.Offset(0, 5).Value
- .psponsor.Value = c.Offset(0, 6).Value
- .pcoordinator.Value = c.Offset(0, 7).Value
- .paims.Value = c.Offset(0, 8).Value
- .pgroup.Value = c.Offset(0, 9).Value
- .pthirdparties.Value = c.Offset(0, 10).Value
- .ptimescale.Value = c.Offset(0, 11).Value
- .poutputs.Value = c.Offset(0, 12).Value
- .prisks.Value = c.Offset(0, 13).Value
- .presources.Value = c.Offset(0, 14).Value
- .pstatus.Value = c.Offset(0, 15).Value
- .cmbAmend.Enabled = True 'allow amendment or
- .cmbDelete.Enabled = True 'allow record deletion
- r = c.Row
- f = 0
- End With
- FirstAddress = c.Address
- Do
- f = f + 1 'count number of matching records
- Set c = .FindNext(c)
- Loop While Not c Is Nothing And c.Address <> FirstAddress
- If f > 1 Then
- Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")
- Case vbOK
- FindAll
- Case vbCancel
- 'do nothing
- End Select
- End If
- Else: MsgBox strFind & " not listed. Please check ID number and try again" 'search failed
- If ws.FilterMode Then ws.ShowAllData
- End If
- End With
- End Sub