Find and Replace, Format and Offset?

[dfolzenlogen]

Does anyone have any suggestions for me?
I have multiple worksheets in my workbook
each worksheet contains 2 columns with text in Column A, numbers in Column B
The length of the columns vary from worksheet to worksheet
The last entry in Column A is always "Total pages"
The last cell in Column B is always empty and on same row as "Total pages" in Column A
I want to loop through all Worksheets to find "Total pages" and replace it with "TOTAL:" , align the text right and make it bold
I then want to move 1 column to the right (using Offset?) to Column B on the same row and autosum all the numbers above that cell, that row.
I then want to make the autosum cell bold and put border of single line on top and double row on bottom (accounting total?)

I found the following code and it gets me as far as looping through all the worksheets using Find and Replace

Sub test()
Dim ws As Worksheet
For Each ws In Worksheets
    With ws
        .Cells.Replace What:="Total", Replacement:="TOTAL:", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
Next ws
End Sub

I added the following to the code but I do not see any results. What am I doing wrong?

Sub test()
Dim ws As Worksheet
For Each ws In Worksheets
    With ws
        .Cells.Replace What:="Total", Replacement:="TOTAL:", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        ActiveCell.Activate
        ActiveCell.Font.Bold = True
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Font.Bold = True
    End With
Next ws
End Sub

I hope I'm on the right track.

Thanks.

 

[JoeMo]

You are off to a good start, but before you replace the text in that last cell you need to locate the cell in col A with your target text. In the code below, I do that by using the Find Method, then if the specific text is found, I replace it with "TOTAL:". Now that you know the location of the cell, it's easy to change its alignment and to calculate a sum and format its col B companion.

Sub test()
Dim ws As Worksheet, R As Range
For Each ws In Worksheets
Application.ScreenUpdating = False
    With ws
        Set R = .Columns("A").Find(What:="Total pages", LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False)
        If Not R Is Nothing Then
            R.Replace "Total Pages", "TOTAL:"
            R.Offset(0, 1).Value = Application.WorksheetFunction.Sum(ws.Range("B1:B" & R.Row - 1))
            R.HorizontalAlignment = xlRight
            With R.Offset(0, 1)
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlDouble
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThick
                End With
                .Font.Bold = True
            End With
        End If
        .Columns("A:B").AutoFit
    End With
    Set R = Nothing
Next ws
Application.ScreenUpdating = True
End Sub

 

[dfolzenlogen]

Thank you Joe! Worked like a charm. Thanks for your help. I hope there's a day when I "get this". I did add 2 lines of code at the end.

Sub test()
Dim ws As Worksheet, R As Range
For Each ws In Worksheets
Application.ScreenUpdating = False
    With ws
        Set R = .Columns("A").Find(What:="total", LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False)
        If Not R Is Nothing Then
            R.Replace "total", "TOTAL:  "
            R.Offset(0, 1).Value = Application.WorksheetFunction.Sum(ws.Range("B1:B" & R.Row - 1))
            R.HorizontalAlignment = xlRight
            With R.Offset(0, 1)
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlDouble
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThick
                End With
                .Font.Bold = True
            End With
        End If
        .Columns("A:B").AutoFit
    End With
    Set R = Nothing
    Next ws
Application.ScreenUpdating = True
End Sub

 

[JoeMo]

Thank you Joe! Worked like a charm. Thanks for your help. I hope there's a day when I "get this". I did add 2 lines of code at the end.

You are welcome - thanks for the reply.