Vba to Find PO and return additional information

[vman5781]

Good morning All,

I am trying to write a VBA to search for either a PO# or Document # and from that return Additional information.

I have a workbook with 2 tabs that this would work with

1st Tab is "Transactions" -- PO # would be -Column "X", Document # is column "V"

2nd Tab is "All Banks" -- Document #would be Column "R", Transaction date- would be -column "G", Vendor Name is column "L", Payment type- would be Column "M", Check # -would be column "J", ACH Transaction # -would be column "O".

3rd Tab would be the userform -- where they would enter the PO# or Document " and it would Return Vendor Name, Document #,Payment type, Payment Date, Check # or Ach Transaction #

Any help with this would be appreciated

Thank you

 

[DanteAmor]

Assuming your data like this:

Transactions

Excel Workbook
	A	U	V	W	X
1			Document #		PO #
2			45001		123
3			45002		123
4			45003		123
5			45004		124
6			45005		125
7			45006		123
8			45007		124
9			45008		125
10			45009		123
11			45010		124
12			45011		125
Sheet

All Banks

Excel Workbook
	A	G	J	L	M	O	R
1		Transaction date	Check #	Vendor Name	Payment type	ACH Transaction	Document #
2		01-sep	ch1	vend1	pay1	ach1	45001
3		02-sep	ch2	vend2	pay2	ach2	45003
4		03-sep	ch3	vend3	pay3	ach3	45005
5		04-sep	ch4	vend4	pay4	ach4	45007
6		05-sep	ch5	vend5	pay5	ach5	45009
7		06-sep	ch6	vend6	pay6	ach6	45001
8		07-sep	ch7	vend7	pay7	ach7	45003
9		08-sep	ch8	vend8	pay8	ach8	45005
10		09-sep	ch9	vend9	pay9	ach9	45007
11		10-sep	ch10	vend10	pay10	ach10	45009
12		11-sep	ch11	vend11	pay11	ach11	45001
13		12-sep	ch12	vend12	pay12	ach12	45003
14		13-sep	ch13	vend13	pay13	ach13	45005
15		14-sep	ch14	vend14	pay14	ach14	45007
16		15-sep	ch15	vend15	pay15	ach15	45009
17		16-sep	ch16	vend16	pay16	ach16	45010
Sheet

3rd Tab

Excel Workbook
	A	B	C	D	E	F
1	PO# / Document #
2	123
3
4	Transaction date	Check #	Vendor Name	Payment type	ACH Transaction	Document #
5	43709	ch1	vend1	pay1	ach1	45001
6	43714	ch6	vend6	pay6	ach6	45001
7	43719	ch11	vend11	pay11	ach11	45001
8	43710	ch2	vend2	pay2	ach2	45003
9	43715	ch7	vend7	pay7	ach7	45003
10	43720	ch12	vend12	pay12	ach12	45003
11	43713	ch5	vend5	pay5	ach5	45009
12	43718	ch10	vend10	pay10	ach10	45009
13	43723	ch15	vend15	pay15	ach15	45009
Sheet

------------------------------------------------------
Put the following code in the events of the sheet

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A2" Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    '
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim r As Range, f As Range, cell As String, dict As Object, ky As Variant, i As Long
    Application.ScreenUpdating = False
    Rows("5:" & Rows.Count).ClearContents
    Set sh1 = Sheets("Transactions")
    Set sh2 = Sheets("All Banks")
    '
    Set dict = CreateObject("scripting.dictionary")
    Set r = sh1.Range("V:V, X:X")
    Set f = r.Find(Target.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
        cell = f.Address
        Do
          dict.Item(sh1.Cells(f.Row, "V")) = Empty
          Set f = r.FindNext(f)
        Loop While Not f Is Nothing And f.Address <> cell
    End If
    '
    i = 5
    For Each ky In dict.keys
      Set r = sh2.Range("R:R")
      Set f = r.Find(ky, , xlValues, xlWhole)
      If Not f Is Nothing Then
          cell = f.Address
          Do
            Cells(i, "A").Value = sh2.Cells(f.Row, "G")
            Cells(i, "B").Value = sh2.Cells(f.Row, "J")
            Cells(i, "C").Value = sh2.Cells(f.Row, "L")
            Cells(i, "D").Value = sh2.Cells(f.Row, "M")
            Cells(i, "E").Value = sh2.Cells(f.Row, "O")
            Cells(i, "F").Value = sh2.Cells(f.Row, "R")
            i = i + 1
            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> cell
      End If
    Next
  End If
End Sub

------------------------------------------------------
SHEET EVENT
Right click the tab of the sheet (3rd Tab) you want this to work, select view code and paste the code into the window that opens up.


------------------------------------------------------
Return to the sheet (3rd Tab), write the PO or Document number in cell A2 and press enter, the data in row 5 will automatically be filled down.

 

[vman5781]

Dante,

Thank you for this, but it does not seem to work. I have verified the Columns for the data and have pasted the code in -- have typed in a valid PO and /or Document # and nothing happens (after hitting enter)

Thank you

 

[DanteAmor]

The code must go in the code of the sheet.

Try my test file.

https://www.dropbox.com/s/zeg280zjlfygovd/trs.xlsm?dl=0