VBA Code Help

[markster]

Hello

I've been working on automating a report to save me lots of time but different systems display the transaction number in a different way. The majority of the data from different systems has the transaction number in the following format so I want to stick with this format TH_G329_S10_CL6_TF60

TH_G320_S10_J10_CL6_TF60

There's one system where it's quite different and I need it to to convert the transaction number to be in he format shown above. The screen shot below shows what I need:

Any help would be much appreciated. Thanks Mark

 

[markster]

Hi DanteAmor

I can't get the second one to run. Would it be possible to adapt the first one to Prefix the reference in column E? so it does the whole reference in one macro. Also, I found some reference numbers that also have just an _M instead of an H_number. So going straight from old type to new type as follows:

Thanks for your continued help. I really need to learn VBA.
Cheers Mark

 

[markster]

Oops sorry forgot to paste the screenprint

 

[DanteAmor]

I can't get the second one to run.

You modified something in the code.
Now it must add the data from column E

 

[DanteAmor]

I don't know if I'm understanding, now column E goes as a suffix?

Try this:

Sub Format_transaction_number()
  Dim a As Variant, b As Variant
  Dim p, p1, p2, p3, p4, q
  Dim i As Long, j As Long, k As Long
  Dim m As String, cad As String
  Dim guion As Long
  
  a = Range("E6", Range("K" & Rows.Count).End(3)).Value2
  ReDim b(1 To UBound(a), 1 To 2)
  
  For i = 2 To UBound(a)
    k = k + 1
    If InStr(1, a(i, 7), ".") > 0 Then
      p = Split(a(i, 7), ".")
      p1 = p(0)
      guion = InStr(1, p(1), "_")
      If guion > 0 Then
         p2 = Left(p(1), guion - 1)
         p3 = Mid(p(1), guion + 1, Len(p(1)))
        
        cad = "TH_" & Left(p2, 1)
        For j = 2 To Len(p2)
          m = Mid(p2, j, 1)
          If m Like "[!0-9]" Then
            cad = cad & "_" & m
          Else
            cad = cad & m
          End If
        Next
        cad = cad & "_CL" & Left(p1, 1) & "_" & Mid(p1, 2, 1) & "F" & Mid(p1, 3)
        b(k, 1) = cad & "_" & a(i, 1)
        If InStr(1, p3, "_") = 0 Then p3 = p3 & "_"
        b(k, 2) = p3
      End If
    Else
      'In case there is no dot
    End If
  Next
  Range("R7").Resize(k, 2).Value = b
End Sub

 

[markster]

It is perfect! - thanks so much. Have a good day. Mark

 

[markster]

Just as a learning point - if I want to change which columns it adds the output e.g New Format Transaction Number currently into Column R & H Number currently into Column S - which part of the code would I need to change? Thanks again

 

[DanteAmor]

if I want to change which columns it adds the outpu

I did a little tweaking so the code makes more sense.
I added some comments to the code, hope that helps.

Sub Format_transaction_number()
Dim a As Variant, b As Variant
Dim p, p1, p2, p3, p4, q
Dim i As Long, j As Long, k As Long
Dim m As String, cad As String
Dim guion As Long

'Load in matrix 'a' columns from A to K (columns 1 to 11)
a = Range("A6", Range("K" & Rows.Count).End(3)).Value2
ReDim b(1 To UBound(a), 1 To 2)

For i = 2 To UBound(a)
k = k + 1

'Check data from column K = 11
If InStr(1, a(i, 11), ".") > 0 Then
p = Split(a(i, 11), ".")
p1 = p(0)
guion = InStr(1, p(1), "_")
If guion > 0 Then
p2 = Left(p(1), guion - 1)
p3 = Mid(p(1), guion + 1, Len(p(1)))

cad = "TH_" & Left(p2, 1)
For j = 2 To Len(p2)
m = Mid(p2, j, 1)
If m Like "[!0-9]" Then
cad = cad & "_" & m
Else
cad = cad & m
End If
Next
cad = cad & "_CL" & Left(p1, 1) & "_" & Mid(p1, 2, 1) & "F" & Mid(p1, 3)

'Add column E = 5 as suffix
b(k, 1) = cad & "_" & a(i, 5)
If InStr(1, p3, "_") = 0 Then p3 = p3 & "_"
b(k, 2) = p3
End If
Else
'In case there is no dot
End If
Next
'Output in R and S
Range("R7").Resize(k, 2).Value = b
End Sub

 

[markster]

Gracias Dante - I am so grateful to you. You have saved me hours and hours of work. I see you are in Mexico - hope that the COVID situation is under control and you and your family have all been well. Take good care. Mark

 

[DanteAmor]

Con mucho gusto. Thanks for the feedback.

Unfortunately, as in many countries, COVID is still not fully controlled. But fortunately my family and I are doing well. Thanks for asking. In the same way I wish you the best. You take care too.