Hi, abjac,
the code you use osnīt what I would call solid and reliable - Iīd go for a UserForm holding the SheetnNames available and a ListBox/ComboBox for the user to make a choice. Same goes for the customer number or Iban being filled on the available data from that sheet. But thatīs my way of coding.
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
Ciao,
Holger
Bookmarks