I have two different TABS: OPERATIONS and DETAILS

OPERATIONS:

DETAILS:

Inside DESCRIPTION field there exist a 11-digit number which is the transaction number (i.e: 19278294999).

DO NOTE that the same cell may contain more than one transaction (i.e: 19266048459 19299490755).

I want to achieve this:

1. VBA SHOULD FIND EVERY TRANSACTION INSIDE "DESCRIPTION" CELL FROM TAB "OPERATIONS". IN THIS CASE THE FIRST ROW CONTAINS ONE TRANSACTION, ROW 2 CONTAINS ONE TRANSACTION AND ROW 3 CONTAINS 2 TRANSACTIONS

2. IT SHOULD COPY THE NUMBER FROM TAB "OPERATIONS" AND PASTE IT INSIDE COLUMN "NUMBER" FROM TAB "DESCRIPTION" (only do this if it is FAC or REC, not a N/C) AND THEN, IT HAS TO DO THE SUMATORY IN TAB "OPERATIONS". TAKE A LOOK:

This should be the expected output after using the code:

I've tried something like this but i am getting a message error like "Object required"

Sub M_snb()
  sn = Sheet1.Cells(1).CurrentRegion
  sp = Sheet2.Cells(1).CurrentRegion
  ReDim sq(100, 4)
    
  sq(0, 0) = sn(1, 1)
  sq(0, 1) = sn(1, 2)
  sq(0, 2) = sn(1, 3)
  sq(0, 3) = "Operation_ID"
  sq(0, 4) = "Amount"
  n = 1
    
  For j = 2 To UBound(sn)
    st = Split(sn(j, 3))
    For jj = 1 To UBound(st)
      sq(n, 0) = sn(j, 1)
      sq(n, 1) = sn(j, 2)
      sq(n, 2) = st(0)
      sq(n, 3) = st(jj)
        
      For jjj = 2 To UBound(sp)
        If CStr(sp(jjj, 1)) = st(jj) Then Exit For
      Next
      sq(n, 4) = sp(jjj, 2)
      n = n   1
    Next
  Next
    
  Sheet1.Cells(1, 8).Resize(UBound(sq), 5) = sq
End Sub

How would you solve this?

EDIT #1

EDIT #3

CodePudding user response：

Here's one way using VBA: Although you mentioned in your instructions regarding N/C in the Type column, that did not seem to make any difference in your pictured results. So it's not in my code, but, if needed, you should be able to add the logic easily enough.

Note that I used Regular Expressions to extract the relevant id numbers from the description column in the first table. I also did the "work" in VBA arrays, as that is much faster than accessing the worksheet repeatedly.

Option Explicit
Sub M_snb()
    Dim vOps As Variant, vDets As Variant
    Dim rOps As Range, rDets As Range
    Dim re As Object, mc As Object, m As Object
    Dim I As Long, K As Long
    Dim vSum, vNumber
    
    
'initialize regex
    Set re = CreateObject("vbscript.regexp")
    With re
        .Global = True
        .Pattern = "(?:\D|\b)(\d{11})(?:\D|\b)"
    End With
    
'read data into variant array for faster processing
'also set the ranges for when we write the results back
With ThisWorkbook.Worksheets("Operations")
    Set rOps = .Cells(1, 1).CurrentRegion
    vOps = rOps
End With
With ThisWorkbook.Worksheets("Details")
    Set rDets = .Cells(1, 1).CurrentRegion
    vDets = rDets
End With

For I = 2 To UBound(vOps, 1)
    vOps(I, 4) = 0
    If re.test(vOps(I, 3)) = True Then
        Set mc = re.Execute(vOps(I, 3))
        For Each m In mc
            For K = 2 To UBound(vDets, 1)
                If m.submatches(0) = CStr(vDets(K, 1)) Then
                    vOps(I, 4) = vOps(I, 4)   vDets(K, 2)
                    vDets(K, 3) = vOps(I, 1)
                End If
            Next K
        Next m
    End If
Next I

'rewrite the tables
    
    With rOps
        .ClearContents
        .Value = vOps
    End With
    
    With rDets
        .ClearContents
        .Value = vDets
    End With
                    
                
End Sub