Move cell based on contents (VBA)

Hi All,

I'm trying to use VBA to move a cell one column to the right if it contains a comma, below is the code I'm using.

My problem is that the code only looks to see if the whole cell value is a comma - I need it to work even if there is other text in the cell with the comma; I tried putting an asterisk either side of the comma in the code but it flagged an error...

Any tips would be appreciated, I'm probably missing something really basic!

Sub MoveComma()
    Dim myrange, cell As Range
    Set myrange = ActiveSheet.Range("K1", Range("K65536").End(xlUp))
    For Each cell In myrange
        If cell.Value = "," Then
            cell.Cut
            Range("K" & Application.WorksheetFunction.Max(2, cell.Row)).Offset(0, 1).Select
            ActiveSheet.Paste
        End If
    Next cell
End Sub

Hi,

Please edit your post to include code tags.

Sub MoveComma()
Dim myrange, cell As Range
Set myrange = ActiveSheet.Range("K1", Range("K65536").End(xlUp))
For Each cell In myrange
If InStr(cell.Value, ",") > 0 Then
cell.Offset(0, 1).Value = cell.Value
cell.ClearContents
End If
Next cell
End Sub

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.