Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate Formula Using Nested IF(ISBLANK)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate Formula Using Nested IF(ISBLANK)
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" wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate Formula Using Nested IF(ISBLANK)
On Oct 18, 9:35 am, "Bob Phillips" wrote:
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" wrote in message ups.com... 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- 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate Formula Using Nested IF(ISBLANK)
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) "Sisilla" wrote in message ps.com... On Oct 18, 9:35 am, "Bob Phillips" wrote: 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" wrote in message ups.com... 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- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF/ISBLANK formula | New Users to Excel | |||
CONCATENATE with CHAR(10) IF NOT ISBLANK | Excel Programming | |||
CONCATENATE with CHAR(10) IF NOT ISBLANK | Excel Programming | |||
ISBLANK Formula Help | Excel Discussion (Misc queries) | |||
Using Concatenate operator to overcome 7 nested IF's | Excel Worksheet Functions |