Check if the string abbreviation and copy the full string. (Vba)

I want to thank this community that has helped me a lot to learn code in vba and solve most of the problems that have occurred to me in the development of my activities. Nothing else in particular.

Now I am working on building data historical exclusions of corporate debt issues in the primary market.

I need macro

Read each cell and check if it contains an abbreviation of many
options and, according to the abbreviation, copy the full value in
the next empty cell of the same row.

Copy Previous string value to "P" for the corresponding
abbreviation found in bullet 1, in the next empy cell of the same row.

Copy all Previous string value to "E" for the corresponding "P" found in
bullet 2 and abbreviation found in bullet 1, in the next empy cell of the
same row.

After this repeat for all the abbreviations found in the cell.

+---+--------------------------------------------------------------------+
| what I have |
+---+--------------------------------------------------------------------+
| | A |
+---+--------------------------------------------------------------------+
| 1 | B.Corp. - 2P. 5E,6E y 9E |
| 2 | B.Corp - 2P, 2E y 5E - C.D.N. 2P 4E |
| 3 | B.Corp. 1P 6E,7E,9E,10E,11E,12E,13E,14E,15E,17E,19E,20E,21E,22E,23E|
| 4 | I.C.P. 2P 5E 6E y 7E - B.Corp. 3P 2E y 3E |
| 5 | I.C.P. 4P 1E- I.C.P 3P 3E- B.Corp. 1P 1E 3E 4E y 6E |
+---+--------------------------------------------------------------------+


For the case of row number 5

+---+-----------------------------------------------------------------------------------------------------------+
| what I need |
+---+--------------------------+---+---+--------------------------+---+---+-----------------+---+---+---+---+---+
| | B | C | D | E | F | G |H | I | J | K | L | M |
+---+--------------------------+---+---+--------------------------+---+---+-----------------+---+---+---+---+---+
| 5 |Instrumento de Corto Plazo| 4 | 1 |Instrumento de Corto Plazo| 3 | 3 |Bono Corporativo | 1 | 1 | 3 | 4 | 6 |
+---+--------------------------+---+---+--------------------------+---+---+-----------------+---+---+---+---+---+


I have dealt with the following codes, but they do not work at all:

Sub abv_to_full()

If InStr(1, Cells(a, 5), "Corp", 1) Or InStr(1, Cells(a, 5), "BC", 1) Then
Cells(a, 5).Offset(0, 1).Value = "Bonos Corporativos" 'English: corporate bonds

ElseIf InStr(1, Cells(a, 5), "C.D.N.", 1) Or InStr(1, Cells(a, 5), "CDN", 1) Then
Cells(a, 5).Offset(0, 1).Value = "Certificados de Depositos" 'English: certificates of deposits / term deposits

ElseIf InStr(1, Cells(a, 5), "I.C.P", 1) Or InStr(1, Cells(a, 5), "ICP", 1) Then
Cells(a, 5).Offset(0, 1).Value = "Instrumentos de Corto Plazo" 'English: short term instruments

ElseIf InStr(1, Cells(a, 5), "BS", 1) Or InStr(1, Cells(a, 5), "Subo", 1) Then
Cells(a, 5).Offset(0, 1).Value = "Bonos Subordinados" 'English: Subordinated Bonds

ElseIf InStr(1, Cells(a, 5), "BAF", 1) Or InStr(1, Cells(a, 5), "B.A.F.", 1) Then
Cells(a, 5).Offset(0, 1).Value = "Bonos de Arrendamiento Financiero" 'English: financial lease bonds

ElseIf InStr(1, Cells(a, 5), "BH", 1) Or InStr(1, Cells(a, 5), "BHIP", 1) Then
Cells(a, 5).Offset(0, 1).Value = "Bonos Hipotecarios" 'English: mortgage securities

ElseIf InStr(1, Cells(a, 5), "IRD", 1) Then
Cells(a, 5).Offset(0, 1).Value = "Instrumentos Representativos de Deuda" 'English: instruments representing debt

End If

End Sub


Sub second_try()
Dim start_, startp_ As Integer
Dim ant_tipo, nvo_tipo

ant_tipo = Array("Corp", "BC", "C.D.N.", "CDN", "I.C.P.", "ICP", "BS", "Subo", "BAF", "B.A.F.", "IRD", "BHIP", "BH")
nvo_tipo = Array("Bonos Corporativos", "Bonos Corporativos", "Certificados de Deposito", "Certificados de Deposito", "Instrumentos de Corto Plazo", "Instrumentos de Corto Plazo", "Bonos Subordinados", "Bonos Subordinados", "Bonos de Arrendamiento Financiero", "Bonos de Arrendamiento Financiero", "Instrumentos Representativos de Deuda", "Bonos Hipotecarios", "Bonos Hipotecarios")

cont = 0
Start = 1
Do
pos = InStr(Start, Cells(a, 5), ant_tipo(i), 0)
If pos > 0 Then
Start = pos + 1 'alternatively: start = pos + Len(srch)
Cells(a, 5).Offset(0, 2 + cont).Value = nvo_tipo(i)
cont = cont + 1
End If
On Error Resume Next
Loop While pos > 0
End sub


thanks in advance.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.