How to find a piece of text inside cells in a range and insert a line break on its left
Category: [Excel] Demo Available
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.
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA code to select if cell contains any text return text in another cell |
| How to crate a macro for text copy and paste in different worksheets based on a variable in Excel |
| How to calculate userform textbox and cell value for label caption |
|
How to create VBA code to count specific texts from different ranges |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.