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
Bookmarks