Combine these two macros so the 2nd macro is called by header names from the 1sf macro

[Tim Hanson]

I am trying to combine these two macros so that I can call the 2nd macro by header names in the 1st macro.

Not getting it.

Thanks

Sub fmt()
    ColList = "Field1,Field2,Field3,Field4"
    ColArray = Split(ColList, ",")
    Set colToFormat = Nothing
    For Each Heading In ColArray
    Set headingFound = Range("A:A").Offset(0, ActiveSheet.Cells.Find(What:=Heading, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Column - 2)
 
      If colToFormat Is Nothing Then Set colToFormat = headingFound Else Set colToFormat = Union(colToFormat, headingFound)
    Next
 
 
    Call RemoveCharList(char As Variant)
 
End Sub

Sub RemoveCharList(char As Variant)
Dim char As Variant, x As Variant
Dim LR As Long, i As Long, j As Long
 
char = Array("]", "{", "%")  '  Change this to suit
LR = Range("A" & Rows.Count).End(xlUp).Row
 
For i = 1 To LR
    With Range("I" & i)
        x = Split(.Value)
        For j = LBound(char) To UBound(char)
            x(UBound(x)) = Replace(x(UBound(x)), char(j), vbNullString)
        Next
        .Value = Join(x)
    End With
Next i
End Sub

 

[Sam Mathai Chacko]

Tim, what exactly is the end goal here? Maybe if you tell us the objective, then one could suggest what and how to do this.

 

[Tim Hanson]

Hello Sam, I want to set a range in the 2 macro

Sub RemoveCharList

with a list of header names.

as the columns I need to use the macro on keep changing there positions in the file on updating but not there header names.

Thanks

 

[Sam Mathai Chacko]

Try this

Sub fmt()

    Const ColList As String = "Field1,Field2,Field3,Field4"
    Dim Heading As Variant, ColArray As Variant
    Dim headingFound As Range
    ColArray = Split(ColList, ",")
    
    For Each Heading In ColArray
        On Error Resume Next
        Set headingFound = Range("1:1").Find(What:=Heading, LookIn:=xlFormulas, LookAt:=xlPart)
        Err.Clear: On Error GoTo 0: On Error GoTo -1
        If headingFound.Column Then RemoveCharList headingFound.Column
    Next
 
End Sub

Sub RemoveCharList(lngColIndex As Long)

    Dim char As Variant, x As Variant
    Dim LR As Long, i As Long, j As Long
     
    char = Array("]", "{", "%")  '  Change this to suit
    LR = Cells(Rows.Count, lngColIndex).End(xlUp).Row
     
    For i = 1 To LR
        With Cells(lngColIndex, i)
            x = Split(.Value)
            For j = LBound(char) To UBound(char)
                x(UBound(x)) = Replace(x(UBound(x)), char(j), vbNullString)
            Next
            .Value = Join(x)
        End With
    Next i
    
End Sub

 

[Tim Hanson]

Hello Sam, trying your code I get

Subscript out of range

x(UBound(x)) = Replace(x(UBound(x)), char(j), vbNullString)

is highlighted in yellow

 

[Sam Mathai Chacko]

Are there any empty cells in the range?

 

[Tim Hanson]

All columns have NO empty cells

 

[Sam Mathai Chacko]

Can you attach a sample file?

 

[Tim Hanson]

Sam
Here is a simple file I created to test the macro, Thanks.

 

[NARAYANK991]

Hi Tim ,

Change the following statement from the existing :

With Cells(lngColIndex,i)

to

With Cells(i, lngColIndex)

Narayan

 

[Sam Mathai Chacko]

Replace Cells(lngColIndex, i) with Cells(i, lngColIndex)

 

[Sam Mathai Chacko]

Bad miss that...thanks Narayan... just saw your post after I posted.

 

[Tim Hanson]

Thank you Sam and Narayan. I uploaded a new workbook the macro is only removing the first instance of a character or only the first character in the array of characters to remove. Is it possible to fix this?, you have helped a lot so I understand if this thread is getting to involved. Thanks again- Tim

 

[Sam Mathai Chacko]

Try using this instead

Sub RemoveCharList(lngColIndex As Long)

    Dim char As Variant, x As Variant
    Dim LR As Long, i As Long, j As Long
     
    char = Array("[", "&", "%")  '  Change this to suit
   LR = Cells(Rows.Count, lngColIndex).End(xlUp).Row
     
    For i = 1 To LR
        With Cells(i, lngColIndex)
            x = .Value
            For j = LBound(char) To UBound(char)
                x = Replace(x, char(j), vbNullString)
            Next
            .Value = x
        End With
    Next i
    
End Sub

 

[Tim Hanson]

Sam, it all works great!!!.

Thanks you!!!