macro to insert think border from Total Value Col A to Col F

[howard]

I have 'Total Value" in Col A 3 rows below the last value and total values in Col B:F , 3 rows after the last value


I have written code to insert a thick outside border from Total value and 5 columns to the right -see sample data below

However when running my macro nothing happens

It would be appreciated if someone cold assist me

Thick Outside Border.xlsx
	A	B	C	D	E	F
12	Total Value	330847	181011	10055	79135	60644
Sheet1

 Sub Totals()
Dim c As Range
With Sheet1
Set c = .Columns(1).Find("Total Value", , , 1)
If Not c Is Nothing Then
c.Offset(, 1).Resize(, 5).Value = "=sum(b1:b" & c.Row - 1 & ")"
Else

lr = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(lr + 3, 1).Value = "Total Value"
.Cells(lr + 3, 2).Resize(, 5).Value = "=sum(b4:b" & lr & ")"
.Cells(lr + 3, 2).Resize(, 5).NumberFormat = "#,##0.00"
.Cells(lr + 3, 2).Resize(, 5).Font.Bold = True
.Cells(lr + 3, 1).Resize(, 5).Borders(xlEdgeLeft).LineStyle = xlContinuous
.Cells(lr + 3, 1).Resize(, 5).Borders(xlEdgeTop).LineStyle = xlContinuous
.Cells(lr + 3, 1).Resize(, 5).Borders(xlEdgeBottom).LineStyle = xlContinuous
.Cells(lr + 3, 1).Resize(, 5).Borders(xlEdgeRight).LineStyle = xlContinuous
.Cells(lr + 3, 1).Resize(, 5).Borders(xlInsideVertical).LineStyle = xlContinuous
.Cells(lr + 3, 1).Resize(, 5).Borders(xlInsideHorizontal).LineStyle = xlContinuous

          
End If
End With
End Sub

 

[Michael M]

Maybe this

Sub Totals()
Dim c As Range
With Sheet1
Set c = .Columns(1).Find("Total Value", , , 1)
If Not c Is Nothing Then
c.Offset(, 1).Resize(, 5).Value = "=sum(b1:b" & c.Row - 1 & ")"
Else

lr = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(lr + 3, 1).Value = "Total Value"
.Cells(lr + 3, 2).Resize(, 5).Value = "=sum(b4:b" & lr & ")"
.Cells(lr + 3, 2).Resize(, 5).NumberFormat = "#,##0.00"
.Cells(lr + 3, 2).Resize(, 5).Font.Bold = True
.Cells(lr + 3, 1).Resize(, 5).Borders.LineStyle = xlContinuous
End If
End With
End Sub

 

[howard]

Thanks for the help, much appreciated

 

[Michael M]

Glad to help...