concatenate three cells as long as there is content in column a

johnmerlino · Nov 29, 2010

Hey all,

I'm trying to convert a formula into a macro. But I am having difficulty having the formula apply to each row as long as there is content in column a. Once column a is empty, then I want to macro to end.

This is what I have:

Code:

Sub prepare4Sweep()

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

     Range("D1:D" & LR).FormulaR1C1 = _
             "=IF(RC[-10]=""=RC[-2] & "" "" & RC[-3] & "" "" & RC[-1]"
     Range("D1:D" & LR).Copy
     Range("E1").PasteSpecial xlPasteValues

End Sub

This doesn't work though. It gives an object-defined error.

Trebor76 · Nov 29, 2010

Hi johnmerlino,

What formula are you trying to put into Column D?

Robert

Smitty · Nov 29, 2010

It looks like your formula is missing some bits. Maybe:

"=IF(RC[-10]="""",RC[-2]&"" ""&RC[-3]&"" ""&RC[-1],0)"

HTH,

johnmerlino · Nov 29, 2010

Thanks for responses. The formula by itself would be this:

[FONT=&quot]=B1 & " " & A1 & " " & C1

I would like to stick it in macro so it iterates through all rows as long as content in column a.

I'm looking for a solution that would work for all similar situations. For example, in another instance I want to apply a different kind of formula to all rows as long as content is in column a. But this produces error as well:

Sub sweepify()
Range("A2").Select
Do Until IsEmpty(ActiveCell)

Range("D2:2" & LR).FormulaR1C1 = _
"=IF(COUNTIF(B:B,A1)>0,"""",A1)"
Range("D3:D" & LR).Copy
Range("E3").PasteSpecial xlPasteValues
ActiveCell.Offset(1, 0).Select
Loop
End Sub
[/FONT]

Trebor76 · Nov 29, 2010

Hi johnmerlino,

See how this goes:

Code:

Dim lngLastRow As Long

    lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    Range("D1:D" & lngLastRow).Formula = _
        "=IF(LEN(A1)>0,B1&"" ""&A1&"" ""&C1,"""")"
        
    Range("D1:D" & lngLastRow).Value = Range("D1:D" & lngLastRow).Value

HTH

Robert

johnmerlino · Nov 29, 2010

It appears to have worked perfectly. Thanks.

Trebor76 · Nov 29, 2010

Thanks for lettimg me know and you're welcome

concatenate three cells as long as there is content in column a

johnmerlino

Board Regular

Excel Facts

Trebor76

Well-known Member

Smitty

Legend

johnmerlino

Board Regular

Trebor76

Well-known Member

johnmerlino

Board Regular

Trebor76

Well-known Member

Similar threads

Forum statistics

Share this page

concatenate three cells as long as there is content in column a

johnmerlino

Board Regular

Excel Facts

Trebor76

Well-known Member

Smitty

Legend

johnmerlino

Board Regular

Trebor76

Well-known Member

johnmerlino

Board Regular

Trebor76

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock