macro supposed to extract cells with comma, it extracts nothing

johnmerlino

Registered User.
Local time
Today, 15:31
Joined
Oct 14, 2010
Messages
81
Hey all,
I have this macro below:
Code:
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.
 
How about using "Text to columns" ??

Code:
    Columns("A:A").Select
    Selection.TextToColumns DataType:=xlDelimited, _
                            Comma:=True
    Range("A1").Select
 
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

Code:
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

Code:
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
 

Users who are viewing this thread

Back
Top Bottom