I want to re-arrange my existing macros in the excel file. I have a macro which goes to the specified sheet and checks column R if there are commas. If it finds the commas it replaces it to dots. Column R is called Email. But in some of documents Email doesn't have to be in the R column - sometimes it's S sometimes in a different place. I have a second macro which does a similar job. The second macro finds the Email column itself (no matter where it was located) and deletes commas at the end of found at the end of the email address. I thought it would be easy to amend this macro with the code from the first one to make it work...but I can't make it to work. Could anyone help me to properply amend the macro?
1-st macro which replaces commas in emails according to Column (R)
Sub GOOD_WORKS_Find_Replace_Commas_in_Emails() Sheets("Data").Activate Dim i As String Dim k As String i = "," k = "." Columns("R").Replace What:=i, Replacement:=k, LookAt:=xlPart, MatchCase:=False Sheets("Automation").Activate MsgBox "Removing commas in emails - Done!" End Sub
2-nd macro which deletes dots at the end of email address (finds email column by itself)
Function getAllColNum(ByVal rowNum As Long, ByVal searchString As Variant) As Object Dim allColNum As Object Dim i As Long Dim j As Long Dim width As Long Set allColNum = CreateObject("Scripting.Dictionary") colNum = 1 With ActiveSheet width = .Cells(rowNum, .Columns.Count).End(xlToLeft).Column For i = 1 To width If InStr(UCase(Trim(.Cells(rowNum, i).Value)), UCase(Trim(searchString))) > 0 Then allColNum.Add i, "" End If ' Next i End With Set getAllColNum = allColNum End Function Sub GOOD_WORKS_No_Dots_at_End_of_Emails() Dim strSearch As String strSearch = "Email" Dim colNum As Variant Dim allColNum As Object Sheets("Data").Activate Dim LR As Long, i As Long Set allColNum = getAllColNum(1, searchString) For Each colNum In allColNum LR = Cells(Rows.Count, colNum).End(xlUp).Row For i = 1 To LR With Range(Cells(i, colNum), Cells(i, colNum)) If Right(.Value, 1) = "." Then .Value = Left(.Value, Len(.Value) - 1) End With Next i Next colNum Sheets("Automation").Activate MsgBox "No Dots at the end of email addresses - Done!" End Sub