• 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.

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

T

Tim Hanson

Member
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

Code: 
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

Code: 
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
 
Hello Sam, I want to set a range in the 2 macro
Sub RemoveCharList
Click to expand...
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
 
Try this

Code: 
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
 
Hello Sam, trying your code I get

Subscript out of range
Code: 
x(UBound(x)) = Replace(x(UBound(x)), char(j), vbNullString)

is highlighted in yellow
 
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
 

Attachments

Try using this instead

Code: 
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
 
You must log in or register to reply here.
Back
Top