Hello,
I am trying to concatenate all cells with a value in a range and combine them into a single string with "*" and "-" as delimiters.
Cells in column D are either blank or filled with numbers and letters. Cells in column C have a value if cells in column D has a value, and have the value "0" otherwise. Cells in collumn B always have a value. I want to loop through the range from first existing value in column D to last, and retrive the cells in all rows where column D have a value. The order of the string should be; Cell B value, Cell C value, Cell D value, todays date, Next row (repeat).
So far I have this code:
Sub test()
Dim lRow As Long
Dim fRow As Long
Dim s As String
Dim r As Long
Dim dt As Date
With ActiveWorkbook.Worksheets("Sheet1").Range("D4:D18")
fRow = Cells(Rows.Count, 1).End(xlDown).Row
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For r = fRow To lRow
If cell.Value <> "" Then
s = cell.Value.Offset(0, -2).Value_
& "*" & cell.Value.Offset(0, -1).Value & "*" & cell.Value & "*" & dt & "-"
Range("Q4") = s
End If
Next r
End With
End Sub
I am trying to concatenate all cells with a value in a range and combine them into a single string with "*" and "-" as delimiters.
Cells in column D are either blank or filled with numbers and letters. Cells in column C have a value if cells in column D has a value, and have the value "0" otherwise. Cells in collumn B always have a value. I want to loop through the range from first existing value in column D to last, and retrive the cells in all rows where column D have a value. The order of the string should be; Cell B value, Cell C value, Cell D value, todays date, Next row (repeat).
So far I have this code:
Sub test()
Dim lRow As Long
Dim fRow As Long
Dim s As String
Dim r As Long
Dim dt As Date
With ActiveWorkbook.Worksheets("Sheet1").Range("D4:D18")
fRow = Cells(Rows.Count, 1).End(xlDown).Row
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For r = fRow To lRow
If cell.Value <> "" Then
s = cell.Value.Offset(0, -2).Value_
& "*" & cell.Value.Offset(0, -1).Value & "*" & cell.Value & "*" & dt & "-"
Range("Q4") = s
End If
Next r
End With
End Sub