VBA Help

[shahid5788]

Hi,

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, 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, 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]




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


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

 

[dUBBINS]

Firstly, I am not a pro.

If your ID's never have anything after, then your code is fine. But it could be cleaned up on this line to ensure it only picks up the C and the 6 digits:

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

 

[shahid5788]

[TABLE="width: 1114"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Hi dUBBINS,

Can you please assist again. I have getting an error message under this line item? I have updated my data a little to 4 line items instead

"Unable to get the Match property of the WorksheetFunction class"

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





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]
[TR]
[TD]ACH DEBIT RECEIVED - Cust ID: Ebay0001234568 Desc: TRANSFER Comp Name: PAYMENT Comp ID: 00000084d SEC: CCD Cust Name: C123455 ENT COMPLE Addenda: No Addenda



[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 573"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD] Name[/TD]
[TD][/TD]
[TD]ID#[/TD]
[/TR]
[TR]
[TD]AUG ENT COMPLEX/NE TIX[/TD]
[TD][/TD]
[TD]C123456[/TD]
[/TR]
[TR]
[TD]PPLCenter/NewEraTicket[/TD]
[TD][/TD]
[TD]C123457[/TD]
[/TR]
[TR]
[TD]WELLSFARGOCTRCOMCASTTIX[/TD]
[TD][/TD]
[TD]C123459[/TD]
[/TR]
[TR]
[TD]Ebay[/TD]
[TD][/TD]
[TD]C123455[/TD]
[/TR]
</tbody>[/TABLE]

 

[dUBBINS]

Is it erring while trying to search in the first three lines you have below "TRANSACTION DESCRIPTION"? Because I do not see any ID's that follow your C###### pattern. And I believe this error is due to the match not existing.

Is z declared as Double? When I try your formula it appears that is how the line of code is reading your info, but again, not a pro at this.

And you probably will get better help if you post this a new thread. That will both follow the rules of this forum and get more people giving it a look. And again, I am far from the most knowledgeable on this site.