I'm new to macros and trying to write a formula within a macro for the first time. I have a large range of data. The number of rows of data will change each time I use the file. The columns could change as well. At the bottom of the data there is a total line but the formula breaks in a sort that I do earlier in the process. I use cells.find to get the row and column numbers for the total line. I am trying to use the variables that come from the cells.find to create my formula but it isn't working right. The result I get in my file is a formula of =sum(359:352086) and the result is #DIV/0!.

I'd like to sum all of the data in the column from row 9 to the line above the total row.

HELP PLEASE!


    Worksheets("detail").Activate
    
    Dim FoundTotalRow2 As Integer
    Dim FoundBILColumn As Integer
    Dim FoundBDRColumn As Integer
    Dim SumBeginIntLoss As Integer
    Dim SumBeginDebRec As Integer
    
    FoundTotalRow2 = Cells.Find(What:="TOTAL BUYOUTS", After:=ActiveCell, LookIn _
        :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
         xlNext, MatchCase:=False, SearchFormat:=False).Row
    
    FoundBILColumn = Cells.Find(What:="Beginning Interest Loss", After:=ActiveCell, LookIn _
        :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
         xlNext, MatchCase:=False, SearchFormat:=False).Column
    
    Cells(FoundTotalRow2, FoundBILColumn).Formula = "=Sum(" & FoundBILColumn & "9" & ":" & FoundBILColumn & FoundTotalRow2 - 1 & ")"
    Cells(FoundTotalRow2, FoundBILColumn).Copy
    Sheets("Check").Select
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
THANK YOU SO MUCH FOR YOUR TIME!