macro supposed to extract cells with comma, it extracts nothing

[johnmerlino]

Hey all,
I have this macro below:

Sub extract_deeds()
Dim LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row

    Range("B1:B" & LR).FormulaR1C1 = _
        "=IF(ISNUMBER(FIND("","", A2)),A2,"""")"
    Range("B1:B" & LR).Copy
    Range("B1").PasteSpecial xlPasteValues
End Sub

It's supposed to search the length of column A, however long or short it may be, and extract cells which contain a comma into column b. If the cell in column a doesn't contain comma, then it just leaves the corresponding b cell empty.
The macro above, however, doesn't pull any cells in column b, despite there being cells with commas in column a.

Thanks for response.

 

[namliam]

How about using "Text to columns" ??

    Columns("A:A").Select
    Selection.TextToColumns DataType:=xlDelimited, _
                            Comma:=True
    Range("A1").Select

 

[Brianwarnock]

What Namliam is suggesting doesn't do what the poster is asking, it creates col A with before the , and col B with to the right of the , .

For the original post I am not sure what this is supposed to do

Range("B1:B" & LR).FormulaR1C1 = _
        "=IF(ISNUMBER(FIND("","", A2)),A2,"""")"

as it will put the same formula in each cell of Col B, plus it is not using R1C1 notation. Why A2?

Just use a simple loop

Brian

Sub extract_deeds()
Dim LR As Long
Dim c As Range

LR = Range("A" & Rows.Count).End(xlUp).Row
   
    For Each c In Range("A1:A" & LR)
      If InStr(c, ",") > 0 Then
       c.Copy
       Cells(c.Row, 2).PasteSpecial
       End If
    Next c
Application.CutCopyMode = False
    
End Sub