Concatenate Formula Using Nested IF(ISBLANK)

[Sisilla]

Hello All,

I am trying to concatenate the values of three cells and set the value
of the first cell to the result. The first cell is always non-empty.
If the second cell is non-empty, I concatenate a line break and its
value with the first cell. If the third cell is non-empty, I
concatenate a line break and its value with the value of the first
cell. The following For Loop runs very slowly-:

For Counter = 1 To LastRow
Set DoneBy = Worksheets("Data2").Cells(Counter, 5)
DoneBy = Worksheets("Data2").Cells(Counter, 5).Text
If Worksheets("Data2").Cells(Counter, 9).Text <> "" Then
DoneBy = DoneBy.Text & Chr(10) &
Worksheets("Data2").Cells(Counter, 9).Text
End If
If Worksheets("Data2").Cells(Counter, 13).Text <> "" Then
DoneBy = DoneBy.Text & Chr(10) &
Worksheets("Data2").Cells(Counter, 13).Text
End If
Next Counter

Is there a faster way to do this? The following code does not work -:

Sheets("Data2").Columns("F:F").Insert
Sheets("Data2").Range("F1:F" & LastRow).FormulaR1C1 = _
"=CONCATENATE(RC[-1], IF(ISBLANK(RC[4], """", CHAR(10)),_
RC[4], IF(ISBLANK(RC[8], """", CHAR(10)), RC[8]) "

I appreciate any effort to help me. Thank you for your time and
consideration.

Sincerely

Sisilla

 

[Bob Phillips]

This might be quicker

With Worksheets("Data2")
For Counter = 1 To LastRow
.Cells(Counter, 5).Value = .Cells(Counter, 5).Text & _
Chr(10) & .Cells(Counter, 9).Text & _
Chr(10) & .Cells(Counter, 13).Text
.Cells(Counter, 5).Value = Replace(.Cells(Counter, 5).Text, _
Chr(10) & Chr(10), Chr(10))
Next Counter
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Sisilla]

This might be quicker

With Worksheets("Data2")
For Counter = 1 To LastRow
.Cells(Counter, 5).Value = .Cells(Counter, 5).Text & _
Chr(10) & .Cells(Counter, 9).Text & _
Chr(10) & .Cells(Counter, 13).Text
.Cells(Counter, 5).Value = Replace(.Cells(Counter, 5).Text, _
Chr(10) & Chr(10), Chr(10))
Next Counter
End With

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hello All,

I am trying to concatenate the values of three cells and set the value
of the first cell to the result. The first cell is always non-empty.
If the second cell is non-empty, I concatenate a line break and its
value with the first cell. If the third cell is non-empty, I
concatenate a line break and its value with the value of the first
cell. The following For Loop runs very slowly-:

For Counter = 1 To LastRow
Set DoneBy = Worksheets("Data2").Cells(Counter, 5)
DoneBy = Worksheets("Data2").Cells(Counter, 5).Text
If Worksheets("Data2").Cells(Counter, 9).Text <> "" Then
DoneBy = DoneBy.Text & Chr(10) &
Worksheets("Data2").Cells(Counter, 9).Text
End If
If Worksheets("Data2").Cells(Counter, 13).Text <> "" Then
DoneBy = DoneBy.Text & Chr(10) &
Worksheets("Data2").Cells(Counter, 13).Text
End If
Next Counter

Is there a faster way to do this? The following code does not work -:

Sheets("Data2").Columns("F:F").Insert
Sheets("Data2").Range("F1:F" & LastRow).FormulaR1C1 = _
"=CONCATENATE(RC[-1], IF(ISBLANK(RC[4], """", CHAR(10)),_
RC[4], IF(ISBLANK(RC[8], """", CHAR(10)), RC[8]) "

I appreciate any effort to help me. Thank you for your time and
consideration.

Sisilla- Hide quoted text -

- Show quoted text -

Thank you, Bob. Your code is a lot faster and a lot less messy as
well. Again, thanks! -Sisilla

 

[Bob Phillips]

Glad to hear that. I didn't have a way to really test it, so it was just
theory <bg>

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

This might be quicker

With Worksheets("Data2")
For Counter = 1 To LastRow
.Cells(Counter, 5).Value = .Cells(Counter, 5).Text & _
Chr(10) & .Cells(Counter, 9).Text & _
Chr(10) & .Cells(Counter, 13).Text
.Cells(Counter, 5).Value = Replace(.Cells(Counter, 5).Text, _
Chr(10) & Chr(10), Chr(10))
Next Counter
End With

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Hello All,

I am trying to concatenate the values of three cells and set the value
of the first cell to the result. The first cell is always non-empty.
If the second cell is non-empty, I concatenate a line break and its
value with the first cell. If the third cell is non-empty, I
concatenate a line break and its value with the value of the first
cell. The following For Loop runs very slowly-:

For Counter = 1 To LastRow
Set DoneBy = Worksheets("Data2").Cells(Counter, 5)
DoneBy = Worksheets("Data2").Cells(Counter, 5).Text
If Worksheets("Data2").Cells(Counter, 9).Text <> "" Then
DoneBy = DoneBy.Text & Chr(10) &
Worksheets("Data2").Cells(Counter, 9).Text
End If
If Worksheets("Data2").Cells(Counter, 13).Text <> "" Then
DoneBy = DoneBy.Text & Chr(10) &
Worksheets("Data2").Cells(Counter, 13).Text
End If
Next Counter

Is there a faster way to do this? The following code does not work -:

Sheets("Data2").Columns("F:F").Insert
Sheets("Data2").Range("F1:F" & LastRow).FormulaR1C1 = _
"=CONCATENATE(RC[-1], IF(ISBLANK(RC[4], """", CHAR(10)),_
RC[4], IF(ISBLANK(RC[8], """", CHAR(10)), RC[8]) "

I appreciate any effort to help me. Thank you for your time and
consideration.

Sisilla- Hide quoted text -

- Show quoted text -

Thank you, Bob. Your code is a lot faster and a lot less messy as
well. Again, thanks! -Sisilla