Results 1 to 5 of 5

Avoid search with wild cars **

Threaded View

abjac Avoid search with wild cars ** 09-02-2014, 05:22 AM
nathansav Re: Avoid search with wild... 09-02-2014, 05:32 AM
lancer102rus Re: Avoid search with wild... 09-02-2014, 05:33 AM
HaHoBe Re: Avoid search with wild... 09-02-2014, 10:30 AM
abjac Re: Avoid search with wild... 09-02-2014, 11:18 AM
  1. #1
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Avoid search with wild cars **

    Hi I have below code which was made brilliant by Holger (HaHoBe). It works fine. The thing is its used so the user seach in hidden sheets, so they are not able to see all the sheet, because is confidential. They only see what they search for.
    But I found out of if I put wild cars in for example the customer number 8 digits and 8 ********. It nearly show all the items of course.
    Is it possible in below code to input, so its impossible to use wild cars* for the search?

    Please see in the code below thanks

    Sincerely Abjac

    Sub EF1034264()
    
    Dim myWord As String
    Dim i As String
    Dim strAddress As String
    Dim ws As Worksheet
    Dim sh As Worksheet
    Dim lngLast As Long
    Dim c As Range
    
    Set sh = ThisWorkbook.Sheets("Result")
    Application.ScreenUpdating = False
    
    i = InputBox("Which sheet do you want to search in. Example: Type spain or italy with small letters")
    On Error Resume Next
    Set ws = Sheets(i)
    On Error GoTo 0
    If ws Is Nothing Then
      MsgBox "No sheet '" & i & "' in workbook"
      Exit Sub
    End If
    
    myWord = InputBox("Input IBAN or Customer nr. to search for")
    If myWord <> "" Then
      lngLast = ws.Range("A" & Rows.Count).End(xlUp).Row
      With ws.Range("C2:C" & lngLast & ",P2:P" & lngLast)
        Set c = .Find(myWord, LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
          ws.Rows(c.Row).Copy sh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
          strAddress = c.Address
          While ActiveCell.Address <> strAddress
            Set c = ws.Range("C2:C" & lngLast & ",P2:P" & lngLast).FindNext(After:=c)
            If c.Address = strAddress Then GoTo exit_Here
            ws.Rows(c.Row).Copy sh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
          Wend
        Else
          MsgBox "Not found, Try again with another IBAN or Customer nr. or exit"
          Exit Sub
        End If
      End With
    Else
      Exit Sub
    End If
    
    exit_Here:
      Set ws = Nothing
      Set sh = Nothing
      MsgBox "IBAN or Customer nr. found see Sheet Result"
    End Sub
    Last edited by abjac; 09-02-2014 at 05:24 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Wild Card Character search using Excel VBA
    By s.anurag1786 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-21-2013, 11:18 PM
  2. Wild Card search with space ??
    By oldtech in forum Excel General
    Replies: 2
    Last Post: 10-14-2008, 10:13 PM
  3. Possible? Trying to do a wild card search...
    By andyd2k in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-05-2006, 01:13 PM
  4. sumproduct with a search and wild card
    By Scorpvin in forum Excel General
    Replies: 1
    Last Post: 06-14-2006, 11:50 AM
  5. Wild Card Search
    By roy.okinawa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-30-2006, 06:45 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