VBA Assistance

[shahid5788]

Hi, I am trying to find a formula which will identify certain criteria. Please see below these are my example transaction description. I have about over 40k transaction line items. I need a formula that will help me pick up transaction descriptions that contain "CHGBCK/ADJ" OR transaction that start with "Cxxxxxxx Ent"

I also would like you do some sort of lookup with that ID "Cxxxxxxx Ent" it can identity the "Name"


I have changed my ID# C1234567 from 7 digits to 6 digits C123456.

Will that change the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> code?



[TABLE="class: cms_table_cms_table, width: 1114"]
<tbody>[TR]
[TD]TRANSACTION DESCRIPTION[/TD]
[/TR]
[TR]
[TD]ACH DEBIT RECEIVED - Cust ID: ABCD Desc: CHGBCK/ADJ Comp Name: AMERICAN EXPRESS Comp ID: 0000000 Batch Discr: PAYMENT DATE 0 SEC: CCD Cust Name: TASK 1 Addenda: No Addenda[/TD]
[/TR]
[TR]
[TD]ACH DEBIT RECEIVED - Cust ID: ABCD Desc: CHGBCK/ADJ Comp Name: AMERICAN EXPRESS Comp ID: 0000022 Batch Discr: PAYMENT DATE 0 SEC: CCD Cust Name: TASK 20 Addenda: No Addenda[/TD]
[/TR]
[TR]
[TD]ACH DEBIT RECEIVED - Cust ID: ABCD Desc: CHGBCK/ADJ Comp Name: AMERICAN EXPRESS Comp ID: 0000070 Batch Discr: PAYMENT DATE 0 SEC: CCD Cust Name: TASK 87 Addenda: No Addenda[/TD]
[/TR]
[TR]
[TD]ACH DEBIT RECEIVED - Cust ID: Ebay0001234567 Desc: TRANSFER Comp Name: PAYMENT Comp ID: 00000054a SEC: CCD Cust Name: C123456 ENT COMPLE Addenda: No Addenda[/TD]
[/TR]
[TR]
[TD]ACH DEBIT RECEIVED - Cust ID: Ebay0001234568 Desc: TRANSFER Comp Name: PAYMENT Comp ID: 00000084d SEC: CCD Cust Name: C123457 ENT COMPLE Addenda: No Addenda[/TD]
[/TR]
[TR]
[TD]ACH DEBIT RECEIVED - Cust ID: Ebay0001234458 Desc: TRANSFER Comp Name: PAYMENT Comp ID: 00000077d SEC: CCD Cust Name: C123459 ENT COMPLE Addenda: No Addenda [/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table, width: 506"]
<tbody>[TR]
[TD]Name[/TD]
[TD][/TD]
[TD][/TD]
[TD]ID#[/TD]
[/TR]
[TR]
[TD]AUG ENT COMPLEX/NE TIX[/TD]
[TD][/TD]
[TD][/TD]
[TD]C1234567[/TD]
[/TR]
[TR]
[TD]PPLCenter/NewEraTicket[/TD]
[TD][/TD]
[TD][/TD]
[TD]C1234578[/TD]
[/TR]
[TR]
[TD]WELLSFARGOCTRCOMCASTTIX[/TD]
[TD][/TD]
[TD][/TD]
[TD]C1234595











[/TD]
[/TR]
</tbody>[/TABLE]

MY Current <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help;">VBA</acronym> CODE
Sub fen()
Dim Tr, C_ID
Tr = Sheets(1).Columns(1).SpecialCells(2).Resize(, 3)
C_ID = Sheets(2).Cells(1).CurrentRegion
With CreateObject("VBScript.Regexp")
.Pattern = "Name: C\d{7}\sENT"
For i = 1 To UBound(Tr)
If InStr(Tr(i, 1), "CHGBCK/ADJ") > 0 Then Tr(i, 2) = "CHB"
If .test(Tr(i, 1)) Then
Set RR = .Execute(Tr(i, 1))
ID = Mid(RR(0), 7, 8)
Z = WorksheetFunction.Match(ID, Application.Transpose(Application.Index(C_ID, 0, 2)))
Tr(i, 3) = Application.Index(C_ID, Z, 1)
End If
Next i
Sheets(3).Cells(1).Resize(UBound(Tr), 3) = Tr
End With
End Sub


The transaction data in sheets(1).cloumns(1), the customers name and ID in sheets(2).columns("A:B") and the results is written in sheets(3).

 

[Joe4]

Duplicate: https://www.mrexcel.com/forum/excel-questions/1002658-visual-basic-applications-help.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you not to bump a thread more than once a day.