• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If Bracket Content In Column A Equals Bracket Content In Column B Then...

I

indi visual

Member
: )

...it's okay I give you permission to laugh at me for this code beneath. But it's the first code I've actually written myself without help...


..although I'm asking for help on it now because it doesn't work (lol). I hope I'm close though I really tried.


What I'm trying to do is replace the bracket content of column A with column B if they're the same. I have to use a wildcard search "{*}" because I'm not sure what will be in the brackets.


Sub Remove_Duplicate_Brace_Content

'''Macro Objective: If duplicate bracket brace content is found in adjacent column B, then delete braces and content from column A.


'Declarations

Dim SpecialCriteria As String

Dim MyRange As Long

Dim i As Long


'Declaration Definitions

SpecialCriteria = "{*}"

MyRange = Range("A2").End(xlDown).Row

For i = 1 To MyRange '''I would like this to search all the way to the last cell row of A


If Cell.Value("i").Column("A", & SpecialCriteria) = Cell.Value("i").Column("B", & SpecialCriteria) Then


'''If the the bracket content in column A matches the bracket brace content in column B then replace bracket braces and containing contents of cell in A


Column("A", i).Replace What:="{*}", Replacement:="", LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False


End If

Next i


End Sub
 
I Think this may be what you want

[pre]
Code: 
Sub Remove_Duplicate_Brace_Content()
'''Macro Objective: If duplicate bracket brace content is found in adjacent column B, then delete braces and content from column A.

'Declarations
Dim MyRange As Long
Dim i As Integer

MyRange = Range("A2").End(xlDown).Row
For i = 2 To MyRange '''I would like this to search all the way to the last cell row of A
If Mid(Cells(i, 1).Value, 2, Len(Cells(i, 1).Value) - 2) = Cells(i, 2).Value Then Cells(i, 1).Value = ""
Next i

End Sub
[/pre]
 
I thank you for the proper of structure of the Len code Hui!


But it looks like I have an error somewhere. My A & B columns appear this way if this extra info helps:


Before:

A B

{01}Blah {02}Blah

{03}Blah {03}Blah

{04}Blah {05}Blah


Based on the macro I'm looking for, it would revise the columns to appear this way (after):


After:

A B

{01}Blah {02}Blah

Blah Blah '''''it deleted "{03}" because both brace contents were same.

{04}Blah {05}Blah


However, I will need to search for it like "{*}" because it may not always be "03".
 
Indi,


Try the code below and let us know if this has served the purpose...


Sub Remove_Duplicate_Brace_Content()

Range("A1").Select

Do While ActiveCell.Value <> "" Or ActiveCell.Value <> vbNullString

If ActiveCell.Value = ActiveCell.Offset(0, 1).Value Then

'both column A and B have the same value

'remove the brace from Column A only

ActiveCell.Value = Mid(ActiveCell.Value, 5, Len(ActiveCell.Value))

End If

ActiveCell.Offset(1, 0).Select

Loop

End Sub


HTH

~Vijay
 
Indi, please tell the whole story when making posts

We are all happy to help but get FRUSTRATED when after investing time you change the rules again.
 
Sub RemoveDup()

Dim xFirst As String

Dim xSecond As String


'Change this next line as needed

For Each c In Range("A2:A100")

xFirst = c

xSecond = c.Offset(0, 1)

If Left(xFirst, 4) = Left(xSecond, 4) Then

c.Value = Mid(xFirst, 5, 999)

c.Offset(0, 1) = Mid(xSecond, 5, 999)

End If

Next c


End Sub
 
@ Hui


I apologize Hui you're right, the last thing I wanna do is get anyone frustrated (I wasn't as necessarily clear as I could've been). You, VJay, and Luke have helped and guided me through things the most and appreciate the time you invest to assist me.


It appears I need to be more clear with my initial intention (and what I'm asking for). I could have been more clear, and for that I apologize. If it means anything it wasn't my intention to alter was I asking help with (or my objective with this macro). In fact I thought I hadn't changed anything


(but you're right I should be more clear moving forward).


Here is my objective for this macro:


If the text (or anything) in the braces (not brackets) are the same in adjacent column b, Then remove both the braces and it's content from column a.


I've attached a PDF screenshot (for visual clarity).


http://www.iandmyself.me/exampleScreenShot.pdf


Variables:

I will not know how long my range is.

I will not know what is contained in the braces.

I will not know what is on the left or right side of the braces.

The braces could be anywhere, with anything on the sides, or potentially nothing on the sides.


What I know:

Some of the brace content will contain the exact same content in col a & col b.


If so, I would like to execute a macro that deletes the braces (and it's content) only while leaving everything else in the cell untouched.


Search will start in A2 down (even though I'm not sure how long down).


Here is also an example workbook with the columns preset (for anyone helping me and experimenting with the code).


http://www.iandmyself.me/exampleSheet.xlsm
 
Do we know for sure that there is only 1 set of brackets in a cell? (and does every cell contain brackets at start?)
 
If above if always true, here's a function and macro to find the braces and compare what's in between. If they are equal, take them out.

Code: 
Function LastRow(x As Integer) As Integer

'Find the last used row in a Column: column A in this example

Application.Volatile

With ActiveSheet

LastRow = .Cells(.Rows.Count, x).End(xlUp).Row

End With

End Function


Sub BraceKiller()

Dim aPos1 As Integer

Dim aPos2 As Integer

Dim bPos1 As Integer

Dim bPos2 As Integer

Dim aWord As String

Dim bWord As String


xRow = LastRow(1)


For Each c In Range("A2", Cells(xRow, 1))

aWord = c.Value

bWord = c.Offset(0, 1).Value


aPos1 = InStr(aWord, "{")

aPos2 = InStr(aWord, "}")

bPos1 = InStr(bWord, "{")

bPos2 = InStr(bWord, "}")


If Mid(aWord, aPos1, aPos2 - aPos1) = Mid(bWord, bPos1, bPos2 - bPos1) Then

c.Value = Left(aWord, aPos1 - 1) & Mid(aWord, aPos2 + 1, 999)

c.Offset(0, 1).Value = Left(bWord, bPos1 - 1) & Mid(bWord, bPos2 + 1, 999)

End If

Next c


End Sub
 
SOLVED!


Done.


Thanks a million everybody, and I think it's time for me to take a break at this point from Chandoo and start doing some more reading-experimenting completely alone before making another post (and asking another question).


I know I started getting on people's nerves a long time ago. lol


Thanks Hui, VJay, and last but certainly not least Luke for the kill shot.
 
Your welcome!

Glad it worked. Don't take be too afraid to post, we don't mind (and I actually enjoy) solving problems in XL. Just work on defining what the whole problem is, including all variables.
 
You must log in or register to reply here.
Back
Top