Good Morning
I wonder if anyone can help me with the code below. It's a search form I have copied from someone online and tried to amend it to suit my spreadsheet. The problem I'm having is setting the search range, I have tried every way of identifying the range that I know of and continually get the same error. The form was originally designed to be used on a table (I have highlighted how the original range was identified) and while I would prefer not to use a table I have also tried that without success.
Any advice is gratefully accepted.
I wonder if anyone can help me with the code below. It's a search form I have copied from someone online and tried to amend it to suit my spreadsheet. The problem I'm having is setting the search range, I have tried every way of identifying the range that I know of and continually get the same error. The form was originally designed to be used on a table (I have highlighted how the original range was identified) and while I would prefer not to use a table I have also tried that without success.
Any advice is gratefully accepted.
Search Practice.xlsm | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
2 | 6506 | New Enquiry | INSTADOOR | Oyestein Machada | Swindon | SN1 | |||||||||||
3 | 6507 | New Enquiry | INSTADOOR | Mick Haren | 14 Rowan Drive Royal Wootton Bassett | ||||||||||||
4 | 6508 | New Enquiry | Construction | Joe Bloggs | 14 Rowan Drive Royal Wootton Bassett | New House | |||||||||||
5 | 6508 | A | Construction | Joe Bloggs | 14 Rowan Drive Royal Wootton Bassett | New House | |||||||||||
6 | 6509 | Groundworks | Mick Haren | New Paving | |||||||||||||
7 | 6510 | New Enquiry | INSTADOOR | Michelle Woodham | Green Street | ||||||||||||
8 | 6510 | A | INSTADOOR | Michelle Woodham | Green Street | ||||||||||||
9 | 6511 | New Enquiry | Construction | Joe Bloggs | New House | ||||||||||||
10 | 6511 | A | Construction | Joe Bloggs | New House | ||||||||||||
11 | 6512 | New Enquiry | INSTADOOR | Graham | |||||||||||||
12 | 6513 | New Enquiry | Construction | Cathy Knutsen | 28 30 Wood Street Swindon | Leak Exploration | |||||||||||
13 | 6513 | A | Construction | Mr G Herbert | Grove House 2 Farm Lane Hannington Wiltshire | Holdcroft Enterprises Ltd | |||||||||||
14 | 6514 | New Enquiry | Construction | Kate Marshall | Russell Cottage Post Office Lane Broad Hinton Swindon | Build retaining wall | |||||||||||
15 | 6515 | New Enquiry | INSTADOOR | Kate Marshall | Russell Cottage Post Office Lane Broad Hinton Swindon | ||||||||||||
JobList |
VBA Code:
Private Sub SearchBtn_Click()
Dim SearchTerm As String
Dim SearchColumn As String
Dim RecordRange As Range
Dim FirstAddress As String
Dim FirstCell As Range
Dim RowCount As Integer
' Display an error if no search term is entered
If REF.Value = "" And Client.Value = "" And Address.Value = "" And PostCode.Value = "" And PhoneNo.Value = "" And Email.Value = "" Then
MsgBox "No search term specified", vbCritical + vbOKOnly
Exit Sub
End If
' Work out what is being searched for
If REF.Value <> "" Then
SearchTerm = REF.Value
SearchColumn = "A"
End If
If Client.Value <> "" Then
SearchTerm = Client.Value
SearchColumn = "E"
End If
If Address.Value <> "" Then
SearchTerm = Address.Value
SearchColumn = "F"
End If
If PostCode.Value <> "" Then
SearchTerm = PostCode.Value
SearchColumn = "G"
End If
If PhoneNo.Value <> "" Then
SearchTerm = PhoneNo.Value
SearchColumn = "H"
End If
If Email.Value <> "" Then
SearchTerm = Email.Value
SearchColumn = "J"
End If
Results.Clear
' Only search in the relevant table column i.e. if somone is searching Location
' only search in the Location column
' With Range("Table1[" & SearchColumn & "]")
With Range("A1:K200" & SearchColumn)
'with Application.Goto Reference:="JobList"
' Find the first match
Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)
' If a match has been found
If Not RecordRange Is Nothing Then
FirstAddress = RecordRange.Address
RowCount = 0
Do
' Set the first cell in the row of the matching value
Set FirstCell = Range("A" & RecordRange.Row)
' Add matching record to List Box
Results.AddItem
Results.List(RowCount, 0) = FirstCell(1, 1)
Results.List(RowCount, 1) = FirstCell(1, 2)
Results.List(RowCount, 2) = FirstCell(1, 3)
Results.List(RowCount, 3) = FirstCell(1, 4)
Results.List(RowCount, 4) = FirstCell(1, 5)
Results.List(RowCount, 5) = FirstCell(1, 6)
RowCount = RowCount + 1
' Look for next match
Set RecordRange = .FindNext(RecordRange)
' When no further matches are found, exit the sub
If RecordRange Is Nothing Then
Exit Sub
End If
' Keep looking while unique matches are found
Loop While RecordRange.Address <> FirstAddress
Else
' If you get here, no matches were found
Results.AddItem
Results.List(RowCount, 0) = "Nothing Found"
End If
End With
End Sub