Dim addr As String
Sheets("ROM Catalog Collection").Unprotect
With Worksheets("ROM Catalog Collection")
Row2 = .range("C4").End(xlDown).Row
For i = 4 To Row2
If .Cells(i, "C").value <> "" Then
.Cells(i, "C").value = Replace(Application.Trim(.Cells(i, "C").value), " ", " ")
End If
If .Cells(i, "C").value <> "" Then
.Cells(i, "C").value = Replace(Application.Trim(.Cells(i, "C").value), """", "'")
End If
If .Cells(i, "C").value <> "" Then
.Cells(i, "C").value = Replace(Application.Trim(.Cells(i, "C").value), "chr(151) or chr (150)", "-")
End If
Next i
End With
With Worksheets("ROM Catalog Collection")
Row2 = .range("D4").End(xlDown).Row
For i = 4 To Row2
If .Cells(i, "D").value <> "" Then
.Cells(i, "D").value = Replace(Application.Trim(.Cells(i, "D").value), " ", " ")
End If
If .Cells(i, "D").value <> "" Then
.Cells(i, "D").value = Replace(Application.Trim(.Cells(i, "D").value), """", "'")
End If
If .Cells(i, "D").value <> "" Then
.Cells(i, "D").value = Replace(Application.Trim(.Cells(i, "D").value), "chr(151) or chr (150)", "-")
End If
Next i
End With
With Worksheets("ROM Catalog Collection")
Row2 = .range("G4").End(xlDown).Row
For i = 4 To Row2
If .Cells(i, "G").value <> "" Then
.Cells(i, "G").value = Replace(Application.Trim(.Cells(i, "G").value), " ", " ")
End If
If .Cells(i, "G").value <> "" Then
.Cells(i, "G").value = Replace(Application.Trim(.Cells(i, "G").value), """", "'")
End If
If .Cells(i, "G").value <> "" Then
.Cells(i, "G").value = Replace(Application.Trim(.Cells(i, "G").value), "chr(151) or chr (150)", "-")
End If
Next i
End With
With Worksheets("ROM Catalog Collection")
Row2 = .range("H4").End(xlDown).Row
For i = 4 To Row2
If .Cells(i, "H").value <> "" Then
.Cells(i, "H").value = Replace(Application.Trim(.Cells(i, "H").value), " ", " ")
End If
If .Cells(i, "H").value <> "" Then
.Cells(i, "H").value = Replace(Application.Trim(.Cells(i, "H").value), """", "'")
End If
If .Cells(i, "H").value <> "" Then
.Cells(i, "H").value = Replace(Application.Trim(.Cells(i, "H").value), "chr(151) or chr (150)", "-")
End If
Next i
End With
(continued for 8 more columns..)
2. Another question i have is, if in a particular cell i have 2 values with '.' (dot) separator i can replace it with ',' (comma) or if there are multiple spaces i can trim it to one space however, if there is 1 value in the same cell its still replacing it with ',' as per the above rule, as for single value i just want to trim with single space or remove the dot if any.
Sub AppMnemonics()
Dim c As range, lr As Long
Sheets("ROM Catalog Collection").Unprotect
lr = Cells(Rows.Count, "J").End(xlUp).Row
With Worksheets("ROM Catalog Collection")
lRow = .range("J4").End(xlDown).Row
For i = 4 To lRow
If .Cells(i, "J").value <> "" Then
.Cells(i, "J").value = WorksheetFunction.Trim(.Cells(i, "J").value)
End If
Next i
End With
For Each c In range("J4:J" & lr)
c.value = Replace(Application.Trim(c), ".", ",")
c.value = Replace(Application.Trim(c), " ", "")
Next c
Thanks!
Bookmarks