Error 1004 problem

[mickyh]

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.

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

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

 

[Engberg]

The line below the one you marked looks wrong, remove & SearchColumn:

With Range("A1:K200")

 

[Fluff]

Try replacing this

With Range("A1:K200" & SearchColumn)

with

With Cells(1, SearchColumn).resize(200)

 

[mickyh]

Hi Endberg

Thank you for your reply, "searchcolumn" indicates which column I want to search. so I do want to refer to it in the range somehow....

 

[Engberg]

Hi Endberg

Thank you for your reply, "searchcolumn" indicates which column I want to search. so I do want to refer to it in the range somehow....

Ye I realised, do what Fluff said

 

[mickyh]

Try replacing this

With Range("A1:K200" & SearchColumn)

with

With Cells(1, SearchColumn).resize(200)

Thanks, Fluff that seems to resolve that problem. Just so I know why, I assume that cell 1 references Row 1 and SearchColumn give's it the column reference with resize 200 indicating the range?
So if SearchColumn =D my search range is then D1:D200?

 

[Fluff]

I assume that cell 1 references Row 1 and SearchColumn give's it the column reference with resize 200 indicating the range?

That's right.

 

[mickyh]

Great Thanks again

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Engberg]

Also, If you think you might go over 200 rows in any column and want to avoid having to update the code manually, or don't want to search more rows than necessary, you could replace 200 with a dynamic row count as below.

LastRow = Cells(Rows.Count, SearchColumn).End(xlUp).row
With Cells(1, SearchColumn).Resize(LastRow)