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

H

HesterPrynne

New Member
Joined
Feb 20, 2017
Messages
42
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]
 
Sort by date Sort by votes
Just created a new file, paste original code from your message. Insert another data and it began to work correctly! Thank you very much:pray:
 
Upvote 0
Hi, Peter_SSs.

How to modify code for such case? Example below. The thing is that in field ":59:"there is no "/" sign as in previous cases and as a result code unable to get this rows in E1.[TABLE="class: cms_table_cms_table"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table_cms_table_cms_table"]
<tbody>[TR]
[TD]10000101[/TD]
[TD="bgcolor: #C5D9F1"]Michael Joseph Jackson[/TD]
[/TR]
</tbody>[/TABLE]

It takes only below field in D1:[TABLE="class: cms_table_cms_table"]
<tbody>[TR]
[TD="bgcolor: #D8E4BC"]:32A:[/TD]
[/TR]
</tbody>[/TABLE]

Result as of now:

[TABLE="class: cms_table_cms_table"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]:20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]EUR [/TD]
[TD="bgcolor: #C5D9F1"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]:23B[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C5D9F1"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="bgcolor: #D8E4BC"]:32A:090216EUR4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFC000"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="bgcolor: #C5D9F1"]:59:10000101[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="bgcolor: #C5D9F1"]Michael Joseph[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="bgcolor: #C5D9F1"]Jackson[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Desired result:


[TABLE="class: cms_table_cms_table"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]:20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]EUR 10000101[/TD]
[TD="bgcolor: #C5D9F1"]Michael Joseph Jackson[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C5D9F1"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="bgcolor: #D8E4BC"]:32A:090216EUR4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFC000"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="bgcolor: #C5D9F1"]:59:10000101[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="bgcolor: #C5D9F1"]Michael Joseph[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="bgcolor: #C5D9F1"]Jackson[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 
Upvote 0
HesterPrynne said:
Hi, The thing is that in field ":59:"[/B][/B]there is no "/" sign as in previous cases and as a result code unable to get this rows in E1.
Click to expand...
See if you can figure out an extra section to tack on to this line
Rich (BB code): 
Case Left(a(i, 1), 5) = ":59:/", Left(a(i, 1), 6) = ":59F:/"
 
Upvote 0
Peter_SSs said:
See if you can figure out an extra section to tack on to this line
Rich (BB code): 
Case Left(a(i, 1), 5) = ":59:/", Left(a(i, 1), 6) = ":59F:/"
Click to expand...
Sorry i don't understand what you are saying. Tried to add to your code Case Left(a(i, 1), 5) = ":59:/", Left(a(i, 1), 6) = ":59F:/" "Left(a(i, 1), 4) = ":59:"". ​ But there is no change and it crashes.
 
Last edited:
Upvote 0
HesterPrynne said:
Sorry i don't understand what you are saying. Tried to add to your code Case Left(a(i, 1), 5) = ":59:/", Left(a(i, 1), 6) = ":59F:/" "Left(a(i, 1), 4) = ":59:" [highlight]".[/highlight] ​ But there is no change and it crashes.
Click to expand...
Looks like you are pretty close to me.
You need a comma between each section like I have between the blue and the red.
You appear to have an [highlight]extra quote mark and full stop[/highlight] at the end that need to be removed
 
Upvote 0
Peter_SSs said:
Looks like you are pretty close to me.
You need a comma between each section like I have between the blue and the red.
You appear to have an [highlight]extra quote mark and full stop[/highlight] at the end that need to be removed
Click to expand...

Case Left(a(i, 1), 5) = ":59:/", Left(a(i, 1), 6) = ":59F:/", Left(a(i, 1), 6) = ":59A:/", Left(a(i, 1), 4) = ":59:"

There is coma and no extra quote mark or full stop in code in Excel. But still appear error " Run-time error '9'. Subscript out of range"
And debugger highlight this row of code " b(k, 1) = b(k, 1) & "" & Split(a(i, 1), "/")(1) "
 
Upvote 0
HesterPrynne said:
Case Left(a(i, 1), 5) = ":59:/", Left(a(i, 1), 6) = ":59F:/", Left(a(i, 1), 6) = ":59A:/", Left(a(i, 1), 4) = ":59:"

There is coma and no extra quote mark or full stop in code in Excel. But still appear error " Run-time error '9'. Subscript out of range"
And debugger highlight this row of code " b(k, 1) = b(k, 1) & "" & Split(a(i, 1), "/")(1) "
Click to expand...
That 'Case' statement looks better now.

You would need to change the next line as well. Try
Code: 
b(k, 1) = b(k, 1) & " " & Replace(Split(a(i, 1), ":")(2), "/", "")
 
Upvote 0
Hi again!
Please advise how can i add to above code condition on which macro will copy stated fields only if block of text at the beggining contains "2:O103"
Sort of :

Select Case True
IF Case Left(a(i, 1), 5) = "2:O103"
Than

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:/", Left(a(i, 1), 6) = ":59A:/", Left(a(i, 1), 4) = ":59:"
b(k, 1) = b(k, 1) & "" & Replace(Split(a(i, 1), ":")(2), "/", "")
b(k, 2) = a(i + 1, 1)


Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Stacking combine of Dynamic Arrays
Replies
4
Views
460
Fluff
Fluff
D
  • Question Question
Find and Remove Specific Text with VBA
Replies
4
Views
2K
Fazza
F
G
  • Question Question
finding cells with value in table and copy Row & column Header & cell Value in new sheet
Replies
4
Views
354
MickG
M
B
  • Question Question
VBA to find sheet and copy rows if cells contain value using reference
Replies
1
Views
613
Fluff
Fluff
I
  • Question Question
Need to copy specific string of text from column into another cell
Replies
5
Views
989
shijith
shijith

Forum statistics

Threads
1,226,812
Messages
6,193,118
Members
453,777
Latest member
Miceal Powell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top