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]

 

[shahid5788]

Hi Jerry,

Sorry for the late replay but I think I was able to modify my own VBA code please see below. I did output the result which I needed. Hopefully you had a similar modification as well with your assistance.


Updated 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 InStr(Tr(i, 1), "DEPOSIT") > 0 Then Tr(i, 2) = "DEPOSIT"
If InStr(Tr(i, 1), "FEE") > 0 Then Tr(i, 2) = "FEE"
If InStr(Tr(i, 1), "AXP DISCNT") > 0 Then Tr(i, 2) = "AXP DISCNT"
If InStr(Tr(i, 1), "SETTLEMENT") > 0 Then Tr(i, 2) = "SETTLEMENT"
If InStr(Tr(i, 1), "COLLECTION") > 0 Then Tr(i, 2) = "COLLECTION"
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