Horizontal cycle (VBA)

F

Fixed

Board Regular
Joined
Apr 28, 2017
Messages
95
Hello!

I have some macro:

Code: 
Sub bracketsSub()
    Dim arr()   As Variant
    Dim x       As Long
    
    x = Cells(Rows.Count, 1).End(xlUp).Row
    arr = Cells(1, 1).Resize(x, 2).Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        arr(x, 2) = brackets(CStr(arr(x, 1)))
    Next x
    Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    Erase arr
End Sub
Private Function brackets(ByRef sss As String) As String
Dim aaa As Long '---
Dim bbb As Long '---
On Error GoTo Error_handler:
     aaa = InStr(sss, "(")
     bbb = InStr(aaa, sss, ")")
     brackets = Mid(sss, aaa + 1, bbb - aaa - 1)
Exit Function
Error_handler:
brackets = "-no-"
End Function

It's extract some text (first found text) from the brackets "()" from the strings in the "A" column and copy to "B" column.
What I should do to extract all texts from the brackets?

Example I want to get:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"](yes)(no)(don't know)<strike></strike>
[/TD]
[TD="align: center"]yes
[/TD]
[TD="align: center"]no
[/TD]
[TD="align: center"]don't know<strike></strike>
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"](yes)(no)<strike></strike>
[/TD]
[TD="align: center"]yes
[/TD]
[TD="align: center"]no
[/TD]
[TD="align: center"]-no-
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"](yes)<strike></strike>
[/TD]
[TD="align: center"]yes
[/TD]
[TD="align: center"]-no-<strike></strike>
[/TD]
[TD="align: center"]-no-<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]

Help me please!
 
Sort by date Sort by votes
MickG said:
This works for both, but it will be limited depending how far you move the goal posts.!!!
Code: 
[COLOR=navy]Sub[/COLOR] MG27Oct53
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, Txt [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Sp [COLOR=navy]As[/COLOR] Variant, Mch
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Mch = Array("yes", "no", "don'[COLOR=green][B]t know")[/B][/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    Dn.Offset(, 1).Resize(, 3) = "-no-"
        [COLOR=navy]For[/COLOR] n = 0 To 2
            [COLOR=navy]If[/COLOR] InStr(Dn.Value, Mch(n)) > 0 [COLOR=navy]Then[/COLOR]
                Dn.Offset(, n + 1) = Mch(n)
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] n
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Click to expand...

Thanx Mick, but in brackets can be any words (symbols).
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Fixed said:
Thanx Rick, I've used your updated macro:

...and it don't work as I showed in sample from the #5 .
Click to expand...
Sorry, I missed that the last example value did not have parentheses around it. Give this modified macro a try instead...
Code: 
[table="width: 500"]
[tr]
	[td]Sub SplitOutOfParentheses()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B1:B" & LastRow) = Evaluate(Replace("IFERROR(IF(A1:A#="""","""",SUBSTITUTE(MID(A1:A#,2,LEN(A1:A#)-2),MID(LEFT(A1:A#&""("",FIND(""("",A1:A#&""("",2)),FIND("")"",A1:A#),200),""("")),A1:A#)", "#", LastRow))
  Columns("B").TextToColumns , xlDelimited, , , False, False, False, False, True, "("
End Sub[/td]
[/tr]
[/table]
By the way, in the future when you get a response that does not work correctly, don't just say "it didn't work"... please tell us in what way it did not work (what happened that shouldn't have happened or, conversely, what did not happen that should have)... this will make it easier for us to track down where the problem is instead of our having to try and figure it out on our own.
 
Upvote 0
Rick Rothstein said:
Sorry, I missed that the last example value did not have parentheses around it. Give this modified macro a try instead...
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub SplitOutOfParentheses()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B1:B" & LastRow) = Evaluate(Replace("IFERROR(IF(A1:A#="""","""",SUBSTITUTE(MID(A1:A#,2,LEN(A1:A#)-2),MID(LEFT(A1:A#&""("",FIND(""("",A1:A#&""("",2)),FIND("")"",A1:A#),200),""("")),A1:A#)", "#", LastRow))
  Columns("B").TextToColumns , xlDelimited, , , False, False, False, False, True, "("
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
By the way, in the future when you get a response that does not work correctly, don't just say "it didn't work"... please tell us in what way it did not work (what happened that shouldn't have happened or, conversely, what did not happen that should have)... this will make it easier for us to track down where the problem is instead of our having to try and figure it out on our own.
Click to expand...

OK, Rick, I will try.

Results of the SplitOutOfParentheses:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD](yes)---(no)--(I don't know)--some text<strike></strike>[/TD]
[TD]yes[/TD]
[TD]no)--<strike></strike>[/TD]
[TD]I don't know)--some tex<strike></strike>[/TD]
[/TR]
[TR]
[TD](yes)--(no)---some text<strike></strike>[/TD]
[TD]yes<strike></strike>[/TD]
[TD]no)---some tex<strike></strike><strike></strike>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]some text<strike></strike>[/TD]
[TD]#REF!<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Example from the post #5 :

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"](yes)---(no)--(I don't know)--some text[/TD]
[TD="align: center"]yes[/TD]
[TD="align: center"]no[/TD]
[TD="align: center"]I don't know<strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"](yes)--(no)---some text[/TD]
[TD="align: center"]yes[/TD]
[TD="align: center"]no[/TD]
[TD="align: center"]-no-[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]some text[/TD]
[TD="align: center"]-no-[/TD]
[TD="align: center"]-no-<strike></strike>[/TD]
[TD="align: center"]-no-[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Fixed said:
Example from the post #5 :

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"](yes)---(no)--(I don't know)--some text[/TD]
[TD="align: center"]yes[/TD]
[TD="align: center"]no[/TD]
[TD="align: center"]I don't know<strike></strike>[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"](yes)--(no)---some text[/TD]
[TD="align: center"]yes[/TD]
[TD="align: center"]no[/TD]
[TD="align: center"]-no-[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]some text[/TD]
[TD="align: center"]-no-[/TD]
[TD="align: center"]-no-<strike></strike>[/TD]
[TD="align: center"]-no-[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Is what I highlighted in red actually what you want from the split out? If not, show me what you do want.
 
Upvote 0
There are can be <= 10 000 symbols in one cell and number of the text parts in the brackets <= 100.
There are can be any text in the brackets - not only "yes", "no", "I don't know".
There are can be any words around the brackets.
 
Upvote 0
Fixed said:
Yes, Rick.
Click to expand...
So you want -no- returned for text not in parentheses, correct?

If so, why in the last example where there is only one piece of text is -no- returned three times? What is the rule for how many times to repeat it?
 
Last edited:
Upvote 0
Rick Rothstein said:
So you want -no- returned for text not in parentheses, correct?

If so, why in the last example where there is only one piece of text is -no- returned three times? What is the rule for how many times to repeat it?
Click to expand...

This is the method to fill the empty cells in those columns that not empty.
 
Upvote 0
Fixed said:
This is the method to fill the empty cells in those columns that not empty.
Click to expand...
Okay, next question... is the only thing the can come between a closing parenthesis ()) and an opening parenthesis (() either nothing or two dashes like all your examples have shown so far? If not, then what can be there... any characters? If so, should they become -no- in the output like the text at the end in your examples?
 
Last edited:
Upvote 0
I wonder if there will be that cases, but if we find it I think we should grab only the text w/o the round brackets "(" text without the round brackets inside ")".

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD](some text(another text))[/TD]
[TD]another text[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Error message when copying and renaming a worksheet
2
Replies
13
Views
297
smozgur
smozgur
R
  • Solved
Extracting first letter
2
Replies
14
Views
515
Joe4
Joe4
T
  • Solved
Sort digits within a cell
2
Replies
13
Views
549
tiredofit
T
M
  • Question Question
Operational Risk Methodology on VBA
Replies
0
Views
81
Mazurofgod
M
E
  • Question Question
Can you combine these 2 macros ?
Replies
6
Views
188
excel4us
E

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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