I have a smartart hierarchy chart and need to run find and replace. On a Google search, couldn't quite find a procedure to do this.
I just need to look at each of the boxes and fine cp: and replace with nothing.
VBA for find and replace in smartart
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA for find and replace in smartart
Something along these lines. Replace "Diagram 1" with the name of your SmartArt chart.
I haven't tested it extensively, so please let me know if and where it fails.
I haven't tested it extensively, so please let me know if and where it fails.
Code: Select all
Sub ReplaceInSmartArt()
Const strWhat = "cp:"
Const strWith = ""
Dim wsh As Worksheet
Dim main As SmartArt
Dim nod As SmartArtNode
Set wsh = ActiveSheet
Set main = wsh.Shapes("Diagram 1").SmartArt
For Each nod In main.AllNodes
nod.TextFrame2.TextRange.Text = Replace(nod.TextFrame2.TextRange.Text, strWhat, strWith)
Next nod
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: VBA for find and replace in smartart
Hi Hans,
It debugs to main.AllNodes and says, "Method or data member not found"
It debugs to main.AllNodes and says, "Method or data member not found"
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA for find and replace in smartart
Hmmm... could you attach a small sample workbook in which the code fails?
(It's past midnight here, I won't be able to look at it immediately)
(It's past midnight here, I won't be able to look at it immediately)
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: VBA for find and replace in smartart
No worries. I can wait and I'll attach something a little later.
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: VBA for find and replace in smartart
Hi Hans,
I tested the code you provided on a brand new worksheet with a smartart and it worked fine. Got me to researching an alternate way.
This works for me. I also added an array to replace multiple strings. Thanks again for the push in the right direction.
I tested the code you provided on a brand new worksheet with a smartart and it worked fine. Got me to researching an alternate way.
This works for me. I also added an array to replace multiple strings. Thanks again for the push in the right direction.
Code: Select all
Sub ReplaceWords()
Dim strWith As String
Dim wsh As Worksheet: Set wsh = ActiveSheet
Dim nod As SmartArtNode
Dim lngIndex As Long
Dim varStr: varStr = Array("cp:", "op:", "ll:", "ay:")
For Each nod In wsh.Shapes(3).SmartArt.AllNodes
For lngIndex = LBound(varStr) To UBound(varStr)
If lngIndex = 0 Then strWith = "" Else strWith = "/ "
nod.TextFrame2.TextRange.Text = Replace(nod.TextFrame2.TextRange.Text, varStr(lngIndex), strWith)
Next lngIndex
Next nod
End Sub
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands