Horizontal cycle (VBA)

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!
 
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

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.
Thanx Rick, I've used your updated macro:

...and it don't work as I showed in sample from the #5 .
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
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.

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
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]
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
Yes, Rick.
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
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?

This is the method to fill the empty cells in those columns that not empty.
 
Upvote 0
This is the method to fill the empty cells in those columns that not empty.
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

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

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