adding a sum formula in the last cell of a column named "fees" in multiple spreadsheets

[mehdi hannechi]

Hey guys,
need your help please
so after splitting a masterfile into different ws, i need to detect a specific column named " fees"( it's location may differ from one sheet to another), go to the last non blank cell in that colmun (i.e D15), then add a sum function in cell D16 summing all entries from D2 to D15.

Thanks in advance !

 

[JGordon11]

If your fees column header is actually " fees" with a space before the word "fees" as you wrote above, then try:

Sub FeesAddSum()
    Dim ws, fees As Range, EndOfFees As Range
    For Each ws In Worksheets
        Set fees = ws.Cells.Find(What:=" fees", After:=[a1], LookIn:=xlFormulas2, LookAt _
            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
        If Not fees Is Nothing Then
            Set EndOfFees = fees.End(xlDown)
            EndOfFees.Offset(1, 0).Formula = "=SUM(" & fees.Offset(1, 0).Address(0, 0) & ":" & EndOfFees.Address(0, 0) & ")"
        End If
    Next ws
End Sub

 

[mehdi hannechi]

If your fees column header is actually " fees" with a space before the word "fees" as you wrote above, then try:

Sub FeesAddSum()
    Dim ws, fees As Range, EndOfFees As Range
    For Each ws In Worksheets
        Set fees = ws.Cells.Find(What:=" fees", After:=[a1], LookIn:=xlFormulas2, LookAt _
            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
        If Not fees Is Nothing Then
            Set EndOfFees = fees.End(xlDown)
            EndOfFees.Offset(1, 0).Formula = "=SUM(" & fees.Offset(1, 0).Address(0, 0) & ":" & EndOfFees.Address(0, 0) & ")"
        End If
    Next ws
End Sub

Hi JGordon11
thank you for your response !
unfortunately, the code did not run
the debugger highlighted as follows :

could you please help ?

thanks in advance

 

[Marc L]

Hi, try this :​

After:=ws.[A1]

but in fact useless so this entire parameter can be removed but keep the commas …​

​

In some old Excel versions xlFormulas2 does not exist so just use xlFormulas.​

​

Another way rather than the Find method is the easy MATCH Excel worksheet function even under VBA …​

 

[mehdi hannechi]

Hi, try this :​

After:=ws.[A1]

but in fact useless so this entire parameter can be removed but keep the commas …​

​

In some old Excel versions xlFormulas2 does not exist so just use xlFormulas.​

​

Another way rather than Range.Find is the easy MATCH Excel worksheet function even under VBA …​

Hi Marc,

thanks for the input ! i changed xlformulas2 to xlformulas and it worked like a charm !!

is there a way to highlight that cell we calculated the sum in (say in yellow) and make it bold ?

 

[Marc L]

A VBA demonstration for starters (where xlFormulas2 should be initially xlValues !) :​

Sub Demo1()
         Dim Ws As Worksheet, Rg(1) As Range
    For Each Ws In Worksheets
           Set Rg(0) = Ws.Cells.Find(" fees", , xlValues, 1, 1)
        If Not Rg(0) Is Nothing Then
           Set Rg(1) = Rg(0).End(xlDown)
          With Rg(1)(2)
              .Font.Bold = True
              .Interior.Color = vbYellow
              .Formula = "=SUM(" & Rg(0)(2).Address & ":" & Rg(1).Address & ")"
          End With
        End If
    Next
         Erase Rg
End Sub

 

[mehdi hannechi]

A VBA demonstration for starters (where xlFormulas2 should be initially xlValues !) :​

Sub Demo1()
         Dim Ws As Worksheet, Rg(1) As Range
    For Each Ws In Worksheets
           Set Rg(0) = Ws.Cells.Find(" fees", , xlValues, 1, 1)
        If Not Rg(0) Is Nothing Then
           Set Rg(1) = Rg(0).End(xlDown)
          With Rg(1)(2)
              .Font.Bold = True
              .Interior.Color = vbYellow
              .Formula = "=SUM(" & Rg(0)(2).Address & ":" & Rg(1).Address & ")"
          End With
        End If
    Next
         Erase Rg
End Sub

YOU ARE THE BEST !!

 

[Marc L]

My demonstration revamped for simplification :​

Sub Demo1r()
         Dim Ws As Worksheet, Rf As Range
    For Each Ws In Worksheets
           Set Rf = Ws.Cells.Find(" fees", , xlValues, 1, 1)
        If Not Rf Is Nothing Then
          With Rf.End(xlDown)(2)
              .Font.Bold = True
              .Interior.Color = vbYellow
              .Formula = "=SUM(" & Rf(2).Address & ":" & .Cells(0).Address & ")"
          End With
        End If
    Next
           Set Rf = Nothing
End Sub

 

[mehdi hannechi]

My demonstration revamped for simplification :​

Sub Demo1r()
         Dim Ws As Worksheet, Rf As Range
    For Each Ws In Worksheets
           Set Rf = Ws.Cells.Find(" fees", , xlValues, 1, 1)
        If Not Rf Is Nothing Then
          With Rf.End(xlDown)(2)
              .Font.Bold = True
              .Interior.Color = vbYellow
              .Formula = "=SUM(" & Rf(2).Address & ":" & .Cells(0).Address & ")"
          End With
        End If
    Next
           Set Rf = Nothing
End Sub

Hi Marc,

i am trying to add a condition that "if the value of Rf.End(xlDown)(2).forumla is > than 10 then to add a new word in the cell under it", could you please help ?

 

[mehdi hannechi]

Hi Marc,

i am trying to add a condition that "if the value of Rf.End(xlDown)(2).forumla is > than 10 then to add a new word in the cell under it", could you please help ?

@JGordon11 - maybe you can also help ?

thanks !