Hi guys,

below is the code I started:

Sub CreditExposureOver10Mio()
    Dim RangeParentCompany As Range
    Dim ParentCompany As Variant
    Dim SumOfNotional As Long
    
      Worksheets("Credit").Activate
    Set RangeParentCompany = Worksheets("Credit").Range("M5", Range("M" & Rows.Count).End(xlUp))
        For Each ParentCompany In RangeParentCompany
            SumOfNotional = WorksheetFunction.Sum("cell.Offset(0, -7).value")
            
        Next
    'Return each parent company with its respective SumOfNotional in cells starting from A1 and going down till list is finished"
End Sub
The range contains a list of names. I want the sub to start with the first name and sum the value(long) which are in 7 columns to the left of the text string. Once this procedure has been done for the first name, it should go back up to the second name and do the same and so on. Once done I will return the name (cell A1 in another sheet) and its respective sum (cell B1 in another sheet) so that I have the list of names with their respective sum.
Could you help on the sum function using an offset of 7 columns to the left?

Thanks!!!