Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CONCATENATE with CHAR(10) IF NOT 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 with CHAR(10) IF NOT ISBLANK
This should be a bit faster:
Dim vArr As Variant Dim i As Long With Worksheets("Data2") With .Cells(1, 5).Resize(.Cells(.Rows.Count, 5).End(xlUp), 9) vArr = .Value For i = 1 To UBound(vArr, 1) vArr(i, 1) = vArr(i, 1) & _ IIf(IsEmpty(vArr(i, 5)), "", Chr(13) & vArr(i, 5)) & _ IIf(IsEmpty(vArr(i, 9)), "", Chr(13) & vArr(i, 9)) Next i .Resize(, 1).Value = vArr End With End With In article om, Sisilla wrote: 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 with CHAR(10) IF NOT ISBLANK
Oops - I was testing in MacXL - for WinXL, change Chr(13) to Chr(10)
In article , JE McGimpsey wrote: This should be a bit faster: Dim vArr As Variant Dim i As Long With Worksheets("Data2") With .Cells(1, 5).Resize(.Cells(.Rows.Count, 5).End(xlUp), 9) vArr = .Value For i = 1 To UBound(vArr, 1) vArr(i, 1) = vArr(i, 1) & _ IIf(IsEmpty(vArr(i, 5)), "", Chr(13) & vArr(i, 5)) & _ IIf(IsEmpty(vArr(i, 9)), "", Chr(13) & vArr(i, 9)) Next i .Resize(, 1).Value = vArr End With End With In article om, Sisilla wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND 1 char in cell of any 3 char =True | Excel Discussion (Misc queries) | |||
CONCATENATE with CHAR(10) IF NOT ISBLANK | Excel Programming | |||
concatenate, char(10), and double quotes | Excel Discussion (Misc queries) | |||
8500 cells with phone number(7 char.), wishing to add area code (10 char.) | Excel Discussion (Misc queries) | |||
How to removed the first three char and last char in XLS | Excel Programming |