Changing range and address

[topi1]

Hi, I have the following working vba.

I tried to chnage the vba to clean columns A and B by making the following change. But it did not work. How should I have done it?
Set rngData = .Range("A1", .Cells(Rows.Count, "A").End(xlUp)) to...
Set rngData = .Range("A1", .Cells(Rows.Count, "B").End(xlUp))

Also, I tried to move to columns M and N to the sheet Start_Clean and change the code as follows. But again, it did not work. Suggestions?
Set rngLookup = .Range("M1", .Cells(Rows.Count, "N").End(xlUp)) to...
Set rngLookup = .Range("Start_Clean!M1", .Cells(Rows.Count, "Start_Clean!N").End(xlUp))

Thank you very much.

Sub Clean_ColumnA()

    Dim wsData As Worksheet
    Dim rngData As Range, rngLookup As Range
    Dim arrData As Variant, arrLookup As Variant
    Dim i As Long
    
    Set wsData = Worksheets("Start2")
    With wsData
        Set rngData = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
        Set rngLookup = .Range("M1", .Cells(Rows.Count, "N").End(xlUp))
    End With
    arrData = rngData.Value
    arrLookup = rngLookup.Value
arrData = Application.Substitute(arrData, ".", ". ")
arrData = Application.Substitute(arrData, "!", "! ")
arrData = Application.Substitute(arrData, "?", "? ")
    arrData = Application.Substitute(arrData, "  ", " ")
    arrData = Application.Substitute(arrData, " .", ".")
    arrData = Application.Substitute(arrData, "..", ".")
    arrData = Application.Substitute(arrData, "[", "")
    arrData = Application.Substitute(arrData, "]", "")
    arrData = Application.Clean(arrData)
    
    For i = 1 To UBound(arrLookup, 1)
        arrData = Application.Substitute(arrData, arrLookup(i, 1), arrLookup(i, 2))
    Next i
    rngData.Value = arrData
    
End Sub

 

[SunnyAlv]

Like This ?

Sub Clean_ColumnA()

    Dim wsData As Worksheet
    Dim rngData As Range, rngLookup As Range
    Dim arrData As Variant, arrLookup As Variant
    Dim i As Long
    
    Set wsData1 = Worksheets("Start2")
    Set wsdata2 = Worksheets("Start_Clean")
    
    With wsData1
        Set rngData = .Range("A1:B1", .Cells(Rows.Count, "A").End(xlUp))
    End With
    
    With wsdata2
        Set rngLookup = .Range("M1", .Cells(Rows.Count, "N").End(xlUp))
    End With


    arrData = rngData.Value
    arrLookup = rngLookup.Value
arrData = Application.Substitute(arrData, ".", ". ")
arrData = Application.Substitute(arrData, "!", "! ")
arrData = Application.Substitute(arrData, "?", "? ")
    arrData = Application.Substitute(arrData, "  ", " ")
    arrData = Application.Substitute(arrData, " .", ".")
    arrData = Application.Substitute(arrData, "..", ".")
    arrData = Application.Substitute(arrData, "[", "")
    arrData = Application.Substitute(arrData, "]", "")
    arrData = Application.Clean(arrData)
    
    For i = 1 To UBound(arrLookup, 1)
        arrData = Application.Substitute(arrData, arrLookup(i, 1), arrLookup(i, 2))
    Next i
    rngData.Value = arrData
    
End Sub

 

[topi1]

Like This ?

Sub Clean_ColumnA()

    Dim wsData As Worksheet
    Dim rngData As Range, rngLookup As Range
    Dim arrData As Variant, arrLookup As Variant
    Dim i As Long
   
    Set wsData1 = Worksheets("Start2")
    Set wsdata2 = Worksheets("Start_Clean")
   
    With wsData1
        Set rngData = .Range("A1:B1", .Cells(Rows.Count, "A").End(xlUp))
    End With
   
    With wsdata2
        Set rngLookup = .Range("M1", .Cells(Rows.Count, "N").End(xlUp))
    End With


    arrData = rngData.Value
    arrLookup = rngLookup.Value
arrData = Application.Substitute(arrData, ".", ". ")
arrData = Application.Substitute(arrData, "!", "! ")
arrData = Application.Substitute(arrData, "?", "? ")
    arrData = Application.Substitute(arrData, "  ", " ")
    arrData = Application.Substitute(arrData, " .", ".")
    arrData = Application.Substitute(arrData, "..", ".")
    arrData = Application.Substitute(arrData, "[", "")
    arrData = Application.Substitute(arrData, "]", "")
    arrData = Application.Clean(arrData)
   
    For i = 1 To UBound(arrLookup, 1)
        arrData = Application.Substitute(arrData, arrLookup(i, 1), arrLookup(i, 2))
    Next i
    rngData.Value = arrData
   
End Sub

Perfect. Worked great! Thank you much.