How to Find a piece of text inside cells in a range and insert a line break on its left.

How to find a piece of text inside cells in a range and insert a line break on its left

Requirement:

The code the user is requiring should run before another piece of code that spits out a PDF File. This file is always messy because there are no line breaks in it and the Auto adjustment of the row doesn’t work fine.

In a report the user has a named ranged contains only one column with 47 rows of data (will always be that), each row can contain an unknown number of time intervals are written like this: (00:00). And they need to be in the beginning of a new line inside the same cell. Other information maybe there too but just these intervals need to be In line break.

After that the user needs to have it to auto fit the rows.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1203668-find-a-piece-of-text-inside-cells-in-a-range-and-insert-a-line-break-on-its-left

Solution:

Code:

Sub LineBreaks()
    Dim x, y, i As Long, ii As Integer, s As String
    
    
    With [ReportDescriptions]
        x = .Value
        For i = 1 To UBound(x, 1)
            x(i, 1) = Replace(x(i, 1), vbLf, "")
            If InStr(1, x(i, 1), "(") > 0 Then
                s = ""
                y = Split(x(i, 1), "(")
                For ii = LBound(y) To UBound(y)
                    If ii = LBound(y) Then
                        s = y(ii) & vbLf & vbLf
                    ElseIf Mid(y(ii), 6, 1) <> ")" Then
                        y(ii) = Replace(y(ii), ")", "")
                        s = s & vbLf & y(ii)
                    ElseIf ii = UBound(y) Then
                        s = s & "(" & y(ii)
                    Else
                        s = s & "(" & y(ii) & vbLf
                    End If
                Next
                x(i, 1) = s
            End If
        Next
        .Value = x
    End With
    Application.ScreenUpdating = 0
    With Sheet1
        .Rows(14).Resize(47).AutoFit
        For i = 14 To 60
            If InStr(1, .Cells(i, 4), vbLf) > 0 Then
                .Rows(i).RowHeight = .Rows(i).RowHeight + 2
            End If
        Next
    End With
    
End Sub

In your cell comment you said "if the driller eventually enters information inside the parentheses", as long as that information is inside parentheses then the above code will place that information as required without manual input from you (the "human" part of the process you mentioned).

Here is the code with comments added to explain each step.

Code:

Sub LineBreaks()
    Dim x, y, i As Long, ii As Integer, s As String
    
    
    With ActiveSheet.[ReportDescriptions]
        '// Load all data in named range "ReportDescriptions" into array x
        x = .Value
        '// loop through array x
        For i = 1 To UBound(x, 1)
            '// If the driller had inserted any line feeds then they need to be removed
            x(i, 1) = Replace(x(i, 1), vbLf, "")
            '// Check if the text contains parentheses
            If InStr(1, x(i, 1), "(") > 0 Then
                s = ""
                '// Split the text for each parenthesis
                '// Load split texts into array y
                y = Split(x(i, 1), "(")
                '// Loop through elements in array y
                '// Build up the string variable s with required number of line feeds
                '// between each element in array y
                For ii = LBound(y) To UBound(y)
                    If ii = LBound(y) Then '// First bit of text, so 2 line feeds after it
                        s = y(ii) & vbLf & vbLf
                    ElseIf Mid(y(ii), 6, 1) <> ")" Then '// Check if more than just a time inside parenthesis
                        y(ii) = Replace(y(ii), ")", "") '// Remove closing bracket after information
                        s = s & vbLf & y(ii)
                    ElseIf ii = UBound(y) Then '// No line feed after last element in array y
                        s = s & "(" & y(ii) '// Add opening bracket (it was removed when text was split
                    Else
                        '// Each time gets its opening bracket replaced and a line feed after its text
                        s = s & "(" & y(ii) & vbLf
                    End If
                Next
                '// the original text without line feeds is replaced with new text with line feeds
                x(i, 1) = s
            End If
        Next
        '// The original text in "ReportDescriptions" is replaced by new text
        .Value = x
    End With
    Application.ScreenUpdating = 0
    With ActiveSheet
        '// Rows 14 resized to 47 rows (i.e row 14 to 60) gets row height adjusted to suit text
        .Rows(14).Resize(47).AutoFit
        '// Loop through each row in "ReportDescriptions" and, if there is 1 or more line feeds increase row height by 1pt
        For i = 14 To 60
            If InStr(1, .Cells(i, 4), vbLf) > 0 Then
                .Rows(i).RowHeight = .Rows(i).RowHeight + 2
            End If
        Next
    End With
    
End Sub

To answer your questions:

If the text is anything other than just a time inside parentheses then that will be the information that the driller may eventually put inside parentheses. Any such information MUST be inside parentheses, otherwise it would be seen as additional text for the previous time that was in parentheses.
Rows(14).Resize(47) means that the range "Row 14" gets increased by 47 rows so covers all rows from Row 14 to Row 60. That is all the rows in the named range "ReportDescriptions"
Yes, you can change Sheet1 to ActiveSheet, I have done that in the code above. Just make sure the sheet than needs to have the line feeds added is the active sheet before running the code.

Obtained from the OzGrid Help Forum.

Solution provided by KjBox.