Macro/VBA Code Help

[shahid5788]

Hi,

Can you please assist with my current VBA code?

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).

I am also getting VBA error message shown below
"Unable to get the Match property of the WorksheetFunction class"
Z = WorksheetFunction.Match(ID, Application.Transpose(Application.Index(C_ID, 0, 2)))







I have changed my ID# 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


[TABLE="class: cms_table_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]C123456[/TD]
[/TR]
[TR]
[TD]PPLCenter/NewEraTicket[/TD]
[TD][/TD]
[TD][/TD]
[TD]C123457[/TD]
[/TR]
[TR]
[TD]WELLSFARGOCTRCOMCASTTIX[/TD]
[TD][/TD]
[TD][/TD]
[TD]C123459[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jerry Sullivan]

Hi shahid5788,

To adjust the code for the change in ID# from 7 digits to 6 digits modify this statement to read...

   .Pattern = "Name: C\d{[B]6[/B]}\sENT"

Your posted description and code describe the ID numbers being in the 2nd Column of Sheet(2). Your screenshot example shows you have those in the 4th column instead.
That is probably the cause of your error message.

 

[shahid5788]

Hi Jerry,

Thank you for the help. I think that did the trick. I have this one Transaction Description that is not getting pick up in my Code. Could you tell why is is not picking up my Name withs its ID#



[TABLE="class: cms_table_cms_table_cms_table, width: 1114"]
<tbody>[TR]
[TD]TRANSACTION DESCRIPTION[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1114"]
<tbody>[TR]
[TD="width: 1114"]ACH DEBIT RECEIVED - Cust ID: Cthba0002567293 Desc: TRANSFER Comp Name: PAYMENTECH Comp ID: 1035801225 SEC: CCD Cust Name: C244743 Sixers Jam Eea Addenda: No Addenda [/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 1178"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 256"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] Name[/TD]
[TD][/TD]
[TD]ID#[/TD]
[/TR]
[TR]
[TD]Sixers Jame Era[/TD]
[TD][/TD]
[TD]C244743[/TD]
[/TR]
</tbody>[/TABLE]

 

[Domenic]

Since it looks like the "ENT" part is optional, try...

.Pattern = "Name: C\d{6}(\sENT)?"

Hope this helps!

 

[Jerry Sullivan]

Since it looks like the "ENT" part is optional, try...

.Pattern = "Name: C\d{6}(\sENT)?"

Hope this helps!

Hi Domenic,

If the ENT is optional, is there any reason not to do...

   .Pattern = "Name: C\d{6}\s"

A couple other changes...

The ID including the "C" should be 7 characters instead of 8

ID = Mid(RR(0), 7, 7)

I'd suggest you use Application consistently instead of WorksheetFunction

Application.Match(ID, Application.Transpose(Application.Index(C_ID, 0, 2)))

 

[Domenic]

Hi Domenic,

If the ENT is optional, is there any reason not to do...

   .Pattern = "Name: C\d{6}\s"

Hi Jerry,

Yeah, that's a great good point. Now that I've taken another quick look at the code it looks like it's not necessary.

Thanks for catching it.

Cheers!

 

[shahid5788]

HI Guys,

Can you help me expand this VBA code a little bit. I am currently picking up all the transaction that contain the pattern with .Pattern = "Name: C\d{6}(\sENT)?".Basically I am telling the VBA code to pick up the items that contain the combination of "Cxxxxxx" and 6 digits after.

I want to expand this to 2 additional argument within my macro. I want to be able to pick up all the transaction and do the same process if they contain "D" and also "F" (shown below). I added additional pattern to the but I am only currently picking only the argument that contain "F" and not longer "C" earlier. Basically I want exactly the same code that was working earlier with "C" argument but also add the argument that contain "D" and "F"


[TABLE="class: cms_table_cms_table_cms_table_cms_table, width: 1114"]
<tbody>[TR]
[TD]TRANSACTION DESCRIPTION[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 989"]
<colgroup><col></colgroup><tbody>[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: Ebay0001234567 Desc: TRANSFER Comp Name: PAYMENT Comp ID: 00000054a SEC: CCD Cust Name: D123456 ENT COMPLE 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: F123456 ENT COMPLE Addenda: No Addenda[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 393"]
<colgroup><col><col span="3"></colgroup><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]C123456[/TD]
[/TR]
[TR]
[TD]Desmond AUG ENT COMPLEX/NE TIX[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]D123456[/TD]
[/TR]
[TR]
[TD]Felix AUG ENT COMPLEX/NE TIX[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]F123456[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jerry Sullivan]

You can modify the RegEx pattern to ...

   .Pattern = "Name: (C|D|F)\d{6}\s"

 

[shahid5788]

Hi Jerry,

Is is possible you can help modify this VBA code a little more. The current VBA code below is extracting all transaction line items that contain in the description with "CHGBCK/ADJ" for example (see below)
[TABLE="width: 1399"]
<tbody>[TR]
[TD="class: xl63, width: 1399"]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]
</tbody>[/TABLE]

Current VBA 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|F)\d{6}\s"
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, 7)
Z = Application.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

I would like to add additional criteria in my current VBA code. Please see transaction description below. In addition to "CHGBCK/ADJ" I would like to pick up all the transaction description that contain "DEPOSIT", "FEE", "APX DISCNT", "SETTLEMENT", "COLLECTION". Please see transaction line items below. Can you please assist?


Additional Transaction Description
[TABLE="width: 1399"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: 5874503 Desc: DEPOSIT Comp Name: PAYMENTECH Comp ID: 1000200225 EEC: CCD Cust Name: EBAYSFARGOCTRTOMCASTTI Addenda: No Addenda [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACH DEBIT RECEIVED - Cust ID: 5444483 Desc: FEE Comp Name: PAYMENTECH Comp ID: 1020445800 TEC: CCD Cust Name: EBAYSFARGOCTTOMMCASTTI Addenda: No Addenda [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACH DEBIT RECEIVED - Cust ID: 9000054LEA Desc: AXP DISCNT Comp Name: AMERICAN EXPRESS Comp ID: 1135472210 Batch Discr: PAYMENT DATE 0006 3 EEC: CCD Cust Name: LEA COUNTY A130547 5786 Addenda: No Addenda [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACH CREDIT RECEIVED - Cust ID: 0005237LEA Desc: SETTLEMENT Comp Name: AMERICAN EXPRESS Comp ID: 1134471280 Batch Discr: PAYMENT DATE 5788 4 SEC: CCD Cust Name: SHE COUNTY A154756 5788 Addenda: No Addenda [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]ACH DEBIT RECEIVED - Cust ID: 00666OWENS Desc: COLLECTION Comp Name: AMERICAN EXPRESS Comp ID: 157452457 Batch Discr: PAYMENT DATE 6666 8 SEC: CCD Cust Name: BOROSBORO CO4678145124 Addenda: No Addenda

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

 

[Jerry Sullivan]

Are you going to need to map each of those Description phrases with a short code as you did for CHGBCK/ADJ -> "CHB"?