Hi,

I thought I'd get a set of experienced eyes on this problem. I am formatting email addresses so there is a ", " at the end of the email. I'm using a For Each Next Loop which the Help system says you use to loop through a range of cells. I am taking everything to to right of the @ sign and concatenating a ", " on the end. I then get everything to the left of the @ sign and finally concatenate everything together.

The problem is after I've selected my range and run the proc, it only returns the very first cell in the range, goes into an infinite loop and doesn't make any changes to the data on the spreadsheet. I don't know why this is happening and need some help.


Sub Testit()
Dim strRange As Range
Dim Cell As Range
Dim strCellVal As String
Dim strRt As String
Dim strLf As String
Dim strRtComma As String
Dim strTrim As String
Dim strFinal As String
Dim intLen As Long
Dim intStrCnt As Long

Set strRange = Selection

For Each Cell In strRange
    strCellVal = ActiveCell.Value
    strTrim = Trim(strCellVal)
  '  intLen = Len(strTrim)
  '  Debug.Print intLen
    If InStr(strTrim, "@") > 0 Then
      intStrCnt = InStr(strTrim, "@")
  '   Debug.Print intStrCnt
        strRt = Mid(strTrim, intStrCnt)
        strRtComma = strRt & ", "
  '   Debug.Print strRtComma
        strLf = Mid(strTrim, 1, (intStrCnt - 1))
  '   Debug.Print strLf
        strFinal = strLf & strRtComma
 '    Debug.Print strFinal
    Else
        Exit For
    End If
Next