VBA Formula Help

[IvenBach]

I have the following formula that I'm working with and have several cells in a column this is applied to. What I would like to do is modify the formula to have it be wrapped with an extra substitute (IE SUBSTITUTE(FORMULA,"W/ Speaker","") so that it will remove another unwanted bit of information.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E32&" - "&U32," Face/","")," Pointer/","")," Bezel",""),"
","-"),"Black","B"),"White","W"),"Blue","L"),"Chrome","C"),"°",""),"Red","R")," Cummins",""),"Trans Temp","Trans")," 7.3L","")," 5.9L",""),", 6.7L",""),"/W/ Speaker","")," Duramax",""),"6.6L ","")," Powerstroke",""),", 6.0L","")," 6.4L",""),"/No Speaker",""),"Pressure","Press")

I tried working on this before the end of day Friday but couldn't come up with a solution. I would add this to the entire column but each row can have a different formula as I started with a generic template and modified it for each grouping to suit my needs. The cell reference is not the same for every row and I attempted to use the following macro to wrap it with my SUBSTITUTE but comes up with #NAME?. Any suggestions?

Sub Macro4()
'
' Macro4 Macro
'
Dim blnTEMP As Boolean
Dim strFORMULA As String

    For Each r In Selection
        blnTEMP = WorksheetFunction.IsNumber(WorksheetFunction.Find("W/ Speaker", r))
        If blnTEMP Then
            strFORMULA = "=SUBSTITUTE(" & Replace(r.Formula, "=", "") & ",""W/ Speaker"",""Speaker"")"
            'Debug.Print strFORMULA
            r.Formula = strFORMULA
        End If
    Next
End Sub

 

[sijpie]

Where it comes up with #NAME, is that on cells with a formula or just with plain text?


I have simplified your macro a little bit. It only shows a #NAME problem if the cell it acts on is plain text with W/ Speaker in it somewhere. If it appears ina formula the macro works fine.

Sub Macro4()
'
'
Dim strFORMULA As String, r As Range
    For Each r In Selection
        If InStr(1, r.Text, "W/ Speaker") Then
        
            strFORMULA = "=SUBSTITUTE(" & Replace(r.Formula, "=", "") & ",""W/ Speaker"",""Speaker"")"
            'Debug.Print strFORMULA
            r.Formula = strFORMULA
        End If
    Next
End Sub

 

[IvenBach]

I figured out the problem that was causing it. SUBSTITUTE was spelled SUBSTITITE. One of those moments that makes you feel oh so smrt