+ Reply to Thread
Results 1 to 3 of 3

.Find Help - Search only in specific columns (or ignore other columns)?

Hybrid View

  1. #1
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    .Find Help - Search only in specific columns (or ignore other columns)?

    Good morning!

    I was hoping for a push in the right directly. Another awesome user told me about .find and he set it up for me for a find button in my spreadsheet. My problem is I wish to only search 2 of the columns on my sheet.

    I have been trying to locate info on my own, see here:
    http://msdn.microsoft.com/en-us/libr...ice.15%29.aspx

    But I cannot seem to find anything showing how to specify where to search. A push in the right direction would be appreciated.

    Here is the code I am using
    Sub CommandButton12_Click()
        Dim str As String, sh1 As Worksheet, c, x As Long, y As Long
        Set sh1 = Sheets("Inventory")
        With sh1.Cells
            str = "*" & InputBox("Find Value") & "*"
            Set c = .Find(str, LookIn:=xlValues)
            x = Application.CountIf(sh1.Cells, str)
            y = x - 1
            If Not c Is Nothing Then
                firstaddress = c.Address
                Do
                    Application.Goto c
                    Msgtext = MsgBox("Found: " & x & " Matches" & vbNewLine & vbNewLine & _
                                     "Remaining: " & y & vbNewLine & vbNewLine & "Goto Next?", vbYesNo, "Itterate Through Found Values")
                    If Msgtext = vbNo Then
                        Exit Sub
                    Else
                        GoTo 1
                    End If
    1
                    Set c = .FindNext(c)
                    y = y - 1
                    If y = 0 Then Application.Goto c: Exit Sub
                Loop While Not c Is Nothing And c.Address <> firstaddress
            End If
        End With
    End Sub
    Thanks in advance!

    Oh forgot, Want to search columns G and J -OR- Search entire sheet but ignore Columns A and B
    Last edited by NewYears1978; 03-25-2014 at 11:47 AM.

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: .Find Help - Search only in specific columns (or ignore other columns)?

    This line right here is defining the search area

    With sh1.Cells
    You would need to change it to match your desire. Example
    With sh1.Range("G1:J5000") 'note: you can specify a last used range here for efficiency

  3. #3
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: .Find Help - Search only in specific columns (or ignore other columns)?

    Thanks! I am slowly learning. Have no VBA training (or even excel) so it's always google and here to try to sort it out.

    Much appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 04-04-2013, 08:02 PM
  2. [SOLVED] how to search for a specific value in different columns and do the assigned task to it?
    By Anatawan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-26-2013, 12:37 AM
  3. [SOLVED] Using range.find to find two specific values in two columns
    By mhrub in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2012, 01:02 PM
  4. Search for 3 specific columns and copy
    By csynic in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-06-2011, 04:43 PM
  5. Formula to search cells/columns containing specific text
    By serenalove in forum Excel General
    Replies: 2
    Last Post: 01-25-2010, 01:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1