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:

[TABLE="width: 124"]
<tbody>[TR]
[TD="width: 165"]:20[/TD]
[/TR]
[TR]
[TD]:23B[/TD]
[/TR]
[TR]
[TD="class: xl63"]:32A:090216EUR4[/TD]
[/TR]
[TR]
[TD]:33B[/TD]
[/TR]
[TR]
[TD]:50A:[/TD]
[/TR]
[TR]
[TD]:52A:[/TD]
[/TR]
[TR]
[TD]:53A:[/TD]
[/TR]
[TR]
[TD]:54A:[/TD]
[/TR]
[TR]
[TD]:56A:[/TD]
[/TR]
[TR]
[TD]:57A:[/TD]
[/TR]
[TR]
[TD="class: xl64"]:59:/10000101[/TD]
[/TR]
[TR]
[TD]:70:[/TD]
[/TR]
[TR]
[TD]:71A:[/TD]
[/TR]
[TR]
[TD]:72:[/TD]
[/TR]
[TR]
[TD]:77B[/TD]
[/TR]
</tbody>[/TABLE]


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;">[TABLE="width: 165"]
<tbody>[TR]
[TD="width: 165"]:32A:100216USD5[/TD]
[/TR]
</tbody>[/TABLE]

 

[S_Wish]

@Peter
I never got a problem with that declaration, so thought that it was just what I wrote. I`ll keep it in mind for further work. Apreciate your attention, Thanks

 

[Peter_SSs]

@Peter
I never got a problem with that declaration,...

No, it isn't really a problem, especially with small codes, but using Variant data type does consume more resources.
Just good practice I think to specifically declare each one in my opinion.

 

[HesterPrynne]

Guys, thanks for your help! Sorry for delay. Both macros works perfect, but in process of use there appeared few not obvious for conditions. I tried to modify macro by myself but it not working after this changes). How can I make macros search and copy not only ":59:/" as stated in first message but ":59F:/" too(two conditions ":59:/" or ":59F:/"). And also is it possible to copy below from ":59:/" row. Lets say macro finds ":59:/" row and it is 60 and macro should copy 61 row and paste it next to D column accordingly?

 

[HesterPrynne]

Hi again, just solved by myself how to copy additional field ":59F:/". But second question left - how to copy next to ":59:/" row?

 

[Peter_SSs]

1. Please post the code that you are currently using, including the change you have made to allow for ":59F:/"
(See my signature block below about using code tags when posting code)

2. Can you also explain more clearly exactly what you mean by "macro should copy 61 row and paste it next to D column accordingly"

3. "Lets say macro finds ":59:/" row and it is 60 and macro should ..." Does this also apply if the macro finds ":59F:/"

 

[HesterPrynne]

Im now using below code given by S_Wish, change I have made to allow for ":59F:/" highlighted.
Point is that macro finds string containing ":59F:/" or ":59:/" and this string in example A11. It should copy not only A11 as in first versions of codes but A12,A13 string too in column E accordingly to column D. After string containing ":59F:/" or ":59:/" it always follows some text and it will be perfect to copy it .

Thank you!


As it works now:

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

Desired result:

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

Sub DoSmth()

    Dim WS As Object
    Dim i, LastRow, CopyRow As Long
    Dim IsEnd As Boolean

    Application.ScreenUpdating = False
    Set WS = ActiveSheet 'you can point any name like
                        ' ... = activeworkbook.sheets("my name")
    LastRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
    IsEnd = True
    i = 1
    CopyRow = 1
    While IsEnd
    If Left(WS.Range("A" & i).Value, 5) = ":32A:" Then
    WS.Range("D" & CopyRow).Value = Mid(WS.Range("A" & i).Value, 12, 3) & " "
    End If

    If Left(WS.Range("A" & i).Value, 5) = ":59:/" Then
    WS.Range("D" & CopyRow).Value = WS.Range("D" & CopyRow).Value & _
    Right(WS.Range("A" & i).Value, Len(WS.Range("A" & i).Value) - 5)
    CopyRow = CopyRow + 1
    End If

    If Left(WS.Range("A" & i).Value, 6) = ":59F:/" Then
    WS.Range("D" & CopyRow).Value = WS.Range("D" & CopyRow).Value & _
    Right(WS.Range("A" & i).Value, Len(WS.Range("A" & i).Value) - 6)
    CopyRow = CopyRow + 1
    End If


    If i > LastRow Then
    IsEnd = False
    End If
    i = i + 1
    Wend

    Application.ScreenUpdating = True

End Sub

 

[Peter_SSs]

More questions I'm afraid:

1. What happened to this requirement? Is it no longer wanted?

Is there any possibility to split results in column D? i mean that now it looks like 10000101EUR and to make change into 10000101 | EUR in two different cells?

2. In post #13 you asked for the row below ":59:/" or ":59F:/" to be copied. In post #16 you are asking for two rows below ":59:/" or ":59F:/" to be joined & copied. Which is it? Is it always the same? If not, how do we decide?

 

[Rick Rothstein]

No, it isn't really a problem, especially with small codes, but using Variant data type does consume more resources.
Just good practice I think to specifically declare each one in my opinion.

Actually, it could be a problem under certain circumstances.

Just so I can narrate, answer the first question with 5 and the second question with 9... the expected sum would be 14, not the 59 that is returned.

Sub Test()
  Dim S1, S2, S3 As Long
  S1 = InputBox("Enter first integer number:")
  S2 = InputBox("Enter second integer number:")
  S3 = S1 + S2
  MsgBox S3
End Sub

 

[HesterPrynne]

More questions I'm afraid:

1. What happened to this requirement? Is it no longer wanted?


2. In post #13 you asked for the row below ":59:/" or ":59F:/" to be copied. In post #16 you are asking for two rows below ":59:/" or ":59F:/" to be joined & copied. Which is it? Is it always the same? If not, how do we decide?

Hi! Sorry for unclear description.

1. Yep, this requirement no longer wanted. It is more convenient to check if it merged, as it presented in code.
2. It always the same. After string containing ":59:/" or ":59F:/" always follows one or two rows with description . And the point is to copy A12 and A13 , join and paste to E1 accordingly to ":59:/" or ":59F:/" field, as it shown in post #16.

 

[Peter_SSs]

2. It always the same. ... always follows one or two rows

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