1

Afternoon

I have lovely working find function working but it searches on all columns. Now I have tried doing various changes to the range definition and also put in a selection restriction for column A.

The problem I'm having is that it is ignoring any restrictions and carries on it's own sweet way. The code is below and it uses two functions the first is Find and the second is Find Next.

Can someone give me a hand and advise how I can restrict the search to just column A of a spreadsheet.

Private Sub Find_Click()
Worksheets("Master").Activate
Dim strFind As String
Dim FirstAddress As String
Dim rSearch As Range
Set rSearch = Range("a1", Range(A:A).End(xlUp))
Dim f      As Integer

strFind = Me.TextBox1.Value

With rSearch
    Set c = .Find(strFind, LookIn:=xlValues)
    If Not c Is Nothing Then
        updateFields anchorCell:=c
        FirstAddress = c.Address
        Do
            f = f + 1
            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
                Case vbCancel

            End Select

        End If
    Else: MsgBox strFind & " not listed"
    End If
End With

Thanks

Community
  • 1
  • 1
Matt W
  • 41
  • 2
  • 6
  • Can you check by doing Ctrl + F in Excel if the option to search in entire workbook (as opposed to current worksheet) is set ? This could cause that. – ApplePie Sep 11 '14 at 12:04

2 Answers2

2

The way you are setting your range is incorrect. First things first, you are missing Double Quotes in A:A. Secondly, Even if you put Double quotes, the rSearch will always be A1

Here are two ways you can set your range

  1. Find Last Row and then create your range

Code 1

Dim lRow As Long

'~~> ws is the relevant sheet
lRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
Set rSearch = ws.Range("A1:A" & lRow)
  1. Take the entire column

Code 2

Set rSearch = ws.Columns(1)
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
0

If you only want to look it column A, try defining the "rsearch" range as:

Range("A1").End(xlDown).Select
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
Beutler
  • 83
  • 1
  • 8
  • 1
    Wrong Advice. You should avoid using `xlDown` You may want to see [THIS](http://stackoverflow.com/questions/25759464/find-and-replace-a-word-from-excel-application-defined-or-object-defined-error/25760089#25760089) – Siddharth Rout Sep 11 '14 at 12:10
  • And Also [THIS](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10718179#10718179) – Siddharth Rout Sep 11 '14 at 12:11
  • @Siddharth Rout Ah, never knew that would be a problem, Thanks for the correction! – Beutler Sep 11 '14 at 12:13