Test this one Les

Sub Find_BP()
Dim rng As Range
Dim LastValueCol As Integer
With Sheets("Sheet1").Range("A:A")
Set rng = .Find(What:="BP", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
If Not rng Is Nothing Then
With Sheets("Sheet1")
LastValueCol = .Cells(rng.Row, Columns.Count).End(xlToLeft).Column
.Cells(rng.Row, LastValueCol + 1).Formula = "=SUM(R" & rng.Row & "C2:R" & rng.Row & "C" & LastValueCol & ")"
End With
Else
MsgBox "Nothing found"
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Les Stout" wrote in message ...
That's 100% thanks Ron, just a further question if one had to look for a
row with a "Heading" in it e.g. "BP', it's location could be in any row
and then go to the last cell in the row, ignoring any blanks and put in
the total as per your script, how would the script change ?

Thanks very much for the help.

best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***