VBA for find and replace in smartart

gailb
2StarLounger
Posts: 148
Joined: 09 May 2020, 14:00

VBA for find and replace in smartart

Post by gailb »

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.

User avatar
HansV
Administrator
Posts: 71216
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA for find and replace in smartart

Post by HansV »

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.

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
Regards,
Hans

gailb
2StarLounger
Posts: 148
Joined: 09 May 2020, 14:00

Re: VBA for find and replace in smartart

Post by gailb »

Hi Hans,

It debugs to main.AllNodes and says, "Method or data member not found"

User avatar
HansV
Administrator
Posts: 71216
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA for find and replace in smartart

Post by HansV »

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)
Regards,
Hans

gailb
2StarLounger
Posts: 148
Joined: 09 May 2020, 14:00

Re: VBA for find and replace in smartart

Post by gailb »

No worries. I can wait and I'll attach something a little later.

gailb
2StarLounger
Posts: 148
Joined: 09 May 2020, 14:00

Re: VBA for find and replace in smartart

Post by gailb »

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.

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

User avatar
HansV
Administrator
Posts: 71216
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA for find and replace in smartart

Post by HansV »

Great! :thumbup:
Regards,
Hans