VBA-Macro to find value inside of string & copy to cell/sheet

[HesterPrynne]

Hello there!,

On sheet i have data in column A. It looks like:

:20

:23B

:32A:090216EUR4

:33B

:50A:

:52A:

:53A:

:54A:

:56A:

:57A:

:59:/10000101

:70:

:71A:

:72:

:77B

<tbody>
</tbody>

And I would like to transform it with help of macros into :
Macros finds row , containing characters":32A:" and copies only currency - in example it is USD,EUR(to another sheet or to next cell, lets say D2).
Than macros finds row , containing characters":59:" and copies all characters that follows after "/"(or ":59:/", lets say E2).
And than it should match Currency(row containing ":32A:") with account (row containing ":59:/") when complete matching within one pair, goes to next and so on.
It should look like:

EUR 10000101

Thanks for your help in advance!

****** id="cke_pastebin" style="position: absolute; top: 40px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

:32A:100216USD5

<tbody>
</tbody>

 

[HesterPrynne]

If it is "always the same", is it one row or is it two rows?

Two rows.

 

[Peter_SSs]

Two rows.

Then give this a try.

Sub GetData()
  Dim a As Variant, b As Variant
  Dim k As Long, i As Long
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 2)
  For i = 1 To UBound(a)
    Select Case True
      Case Left(a(i, 1), 5) = ":32A:"
        k = k + 1: b(k, 1) = Mid(a(i, 1), 12, 3)
      Case Left(a(i, 1), 5) = ":59:/", Left(a(i, 1), 6) = ":59F:/"
        b(k, 1) = b(k, 1) & " " & Split(a(i, 1), "/")(1)
        b(k, 2) = a(i + 1, 1) & " " & a(i + 2, 1)
    End Select
  Next i
  With Range("D2:E2").Resize(k)
    .Value = b
    .Columns.AutoFit
  End With
End Sub

 

[HesterPrynne]

It copy all needed fields in case of ":59:/" correctly. And gets only currency code ( field containing ":32A:") if met ":59F:/" field.

 

[Peter_SSs]

It copy all needed fields in case of ":59:/" correctly. And gets only currency code ( field containing ":32A:") if met ":59F:/" field.

I don't understand what you are saying. If it is not working correctly, please post a small sample of data for which my code fails and also post the results that you expect from that sample data.

 

[HesterPrynne]

Excel 2010

Result in case if macro finds in A11 ":59:/"

	A	B	C	D	E
1	:20			EUR 10000101	Michael Joseph Jackson
2	:23B
3	:32A:090216EUR4
4	:33B
5	:50A:
6	:52A:
7	:53A:
8	:54A:
9	:56A:
10	:57A:
11	:59F:/10000101
12	Michael Joseph
13	Jackson
14	:70:
15	:71A:
16	:72:
17	:77B

<tbody>
</tbody>

Result in case if macro finds in A11 ":59F:/"

	A	B	C	D	E
1	:20			EUR
2	:23B
3	:32A:090216EUR4
4	:33B
5	:50A:
6	:52A:
7	:53A:
8	:54A:
9	:56A:
10	:57A:
11	:59F:/10000101
12	Michael Joseph
13	Jackson
14	:70:
15	:71A:
16	:72:
17	:77B

<tbody>
</tbody>

Desirable result is - both ":59:/" and ":59F:/" gets information correctly, not only ":59:/". Macro for ":59F:/" in A11 should get information as ":59:/" in A11.

It seems like part of code, that belongs to ":59F:/" not working for some reasons.

 

[Peter_SSs]

I still don't understand. The values in A1:A17 of both your tables are identical, yet you show two different desired results.

 

[HesterPrynne]

Ohh, sorry. . Now its not identical))

Excel 2010

Result in case if macro finds in A11 ":59:/"

	A	B	C	D	E
1	:20			EUR 10000101	Michael Joseph Jackson
2	:23B
3	:32A:090216EUR4
4	:33B
5	:50A:
6	:52A:
7	:53A:
8	:54A:
9	:56A:
10	:57A:
11	:59:/10000101		<---- ":59:/".
12	Michael Joseph
13	Jackson
14	:70:
15	:71A:
16	:72:
17	:77B

<tbody>
</tbody>

 

[Peter_SSs]

Ohh, sorry. . Now its not identical))

Excel 2010

Result in case if macro finds in A11 ":59:/"

	A	B	C	D	E
1	:20			EUR 10000101	Michael Joseph Jackson
2	:23B
3	:32A:090216EUR4
4	:33B
5	:50A:
6	:52A:
7	:53A:
8	:54A:
9	:56A:
10	:57A:
11	:59:/10000101		<---- ":59:/".
12	Michael Joseph
13	Jackson
14	:70:
15	:71A:
16	:72:
17	:77B

<tbody>
</tbody>

From that sample data, my code produces exactly that result, only it puts the result in row 2, not row 1. Is that the problem?

 

[HesterPrynne]

From that sample data, my code produces exactly that result, only it puts the result in row 2, not row 1. Is that the problem?

Yes!But in case if macro finds in A11 ":59F:/"

	A	B	C	D	E
1	:20			EUR
2	:23B
3	:32A:090216EUR4
4	:33B
5	:50A:
6	:52A:
7	:53A:
8	:54A:
9	:56A:
10	:57A:
11	:59F:/10000101		<--:59F/:
12	Michael Joseph
13	Jackson
14	:70:
15	:71A:
16	:72:
17	:77B

<tbody>
</tbody>

And those results not similar!) Difference in one letter. It works for ":59:/" and dont work for ":59F:/".

 

[Peter_SSs]

It works for ":59:/" and dont work for ":59F:/".

Seems to work for me. I copied column A from post #29 and pasted in column A below then ran the code from post #22.
The code produced the results shown in D2:E2 below. Isn't that what you want it to do?

Excel 2010 32 bit
	A	B	C	D	E
1	:20
2	:23B			EUR 10000101	Michael Joseph Jackson
3	:32A:090216EUR4
4	:33B
5	:50A:
6	:52A:
7	:53A:
8	:54A:
9	:56A:
10	:57A:
11	:59F:/10000101
12	Michael Joseph
13	Jackson
14	:70:
15	:71A:
16	:72:
17	:77B
Sheet4