Working with simple multilined cells

Hello,
I'm having a really tough time trying to do some string manipulation.

I realise that working with strings is generally quite straight forward, but I've tried to make this loop work properly for about three days and I'm just not getting anywhere.

Ultimately, the script simply needs to identify the specified line within a cell and insert new data on that line (the system date in this case.)

Ideally the routine would bail out if the particular line number does not exist in the cell, but I'll make it do that once I've got it inserting the new data into the correct place.

The code is as follows and I have attached the workbook:

Sub DoIt()
    LeadString = vbNullString
    'The cell's original data:
    TailString = Range("A2").Value
    If Range("B1").Value = 1 Then
        'Just insert data before the first linefeed:
        Range("A2").Value = Date & Range("A2").Value
    Else
        For DateLine = 2 To Range("B1").Value
            'Assemble the preceeding string:
            LeadString = LeadString & Left(TailString, InStr(TailString, vbLf))
            'Set the trailing data:
            TailString = Mid(TailString, Len(LeadString) + 1)
        Next DateLine
        'Rewrite cell contents to include the current date in the position established above:
        Range("A2").Value = LeadString & Date & TailString
    End If
End Sub

Thanks for your consideration,

- Gordon

Gordon

Try

Sub aaa()
  If Range("B1").Value = 1 Then
    Range("A2").Value = Date & vbLf & Range("A2").Value
  Else
    Range("A2").Value = WorksheetFunction.Substitute(Range("A2").Value, vbLf, Date & vbLf, Range("B1").Value)
  End If
End Sub

rylo

Wow that's a beautiful function!

I have no idea how often I could have put it to good uses in the past, but I had no idea that it existed.

Thank You for the assistance and enlightenment rylo, this will be a function I'll be using almost every day now!

- Gordon