1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
| Sub ChercheApproval()
On Error GoTo MyErrorHandler:
Dim txt_Recherche As String
Dim Res1, Res2, Res3, Res4, Res5, Res6, Res7, Res8 As Variant
txt_Recherche = UserForm1.TextBox1.Value
Sheets("MyDatabase").Range("H6:H35").Select
Selection.Copy
Sheets("MyDatabase2").Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("MyDatabase").Range("A6:G35").Select
Selection.Copy
Sheets("MyDatabase2").Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
If Len(txt_Recherche) > 0 Then
Res1 = Application.WorksheetFunction.VLookup(txt_Recherche, Sheets("MyDatabase2").Range("A6:H15"), 1, False) ' Descripion
Res2 = Application.WorksheetFunction.VLookup(txt_Recherche, Sheets("MyDatabase2").Range("AB6:H15"), 2, False) ' Categories
Res3 = Application.WorksheetFunction.VLookup(txt_Recherche, Sheets("MyDatabase2").Range("A6:H15"), 3, False) ' Types
Res4 = Application.WorksheetFunction.VLookup(txt_Recherche, Sheets("MyDatabase2").Range("A6:H15"), 4, False) ' Intercompany
Res5 = Application.WorksheetFunction.VLookup(txt_Recherche, Sheets("MyDatabase2").Range("A6:H15"), 5, False) ' Invoice Amount
Res6 = Application.WorksheetFunction.VLookup(txt_Recherche, Sheets("MyDatabase2").Range("A6:H15"), 6, False) ' Inv. Curr.
Res7 = Application.WorksheetFunction.VLookup(txt_Recherche, Sheets("MyDatabase2").Range("A6:H15"), 7, False) ' Status
Res8 = Application.WorksheetFunction.VLookup(txt_Recherche, Sheets("MyDatabase2").Range("B6:H15"), 8, False) ' Data Payment
UserForm1.txt_Supplier.Value = Res2
UserForm1.txt_InvoiceNum.Value = Res3
UserForm1.txt_Description.Value = Res4
UserForm1.txt_DateInv.Value = Res5
UserForm1.txt_DateRec.Value = Res6
UserForm1.txt_InvoiceAmount.Value = Res7
UserForm1.txt_InvoiceCurr.Value = Res8
UserForm1.txt_ApprovalNum.Value = Res1
Else
MsgBox Application.UserName & "Digitar um numero da AFP ou" & vbNewLine & "um numero da factura"
End If
Exit Sub
MyErrorHandler:
If Err.Number = 1004 Then
MsgBox Application.UserName & vbNewLine & "O dado procurado nao foi encontrado"
End If
End Sub |
Partager