I have written a macro to pull and trim 3 lines of a title from an original worksheet if they were seperated during an earlier portion of the macro. The titles occur at multiple points in the page (i'm formatting a premade report). However i am stuck in an infinite loop now. any suggestions?
Sub Titles()
Application.DisplayAlerts = False
Dim FirstAddress As String
With ActiveSheet.Cells
Set c = Cells.Find(What:="page", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Cells(c.Row, 13).FormulaR1C1 = "=TRIM(MID(Sheet1!RC[-12],6,215))"
Cells(c.Row + 1, 13).FormulaR1C1 = "=MID(Sheet1!RC[-12],6,215)"
Cells(c.Row + 2, 13).FormulaR1C1 = "=MID(Sheet1!RC[-12],6,215)"
Cells(c.Row, 1).Value = Cells(c.Row, 13).Value
Cells(c.Row + 1, 1).Value = LTrim(Cells(c.Row + 1, 13).Value)
Cells(c.Row + 2, 1).Value = LTrim(Cells(c.Row + 2, 13).Value)
Range(Cells(c.Row, 1), (Cells(c.Row, 12))).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
Range(Cells(c.Row + 1, 1), (Cells(c.Row + 1, 12))).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
Range(Cells(c.Row + 2, 1), (Cells(c.Row + 2, 12))).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Columns("M:M").Delete
Application.DisplayAlerts = True
End Sub
Bookmarks