0

I have a selected range that I defined as my range. I want to get the Sum of this selection in a specific cell. The makro shall find "x", select the cell below and put in "Sum" + the range I defined in "myrange"

Sub more_twelve_months()
Dim myrange As Range

Set myrange = Range(Range("F5"), Range("F5").End(xlToRight))
       
Set more_twelve_months = Range("A1:ZZ10000").Find("x")
    more_twelve_months.Select
    FormularCell = ActiveCell.Offset(1, 0).Select
    Selection.Resize(Selection.Rows.Count, _
    Selection.Columns.Count).Select


   ActiveCell.Sum (myrange)

I tried several ways to get the sum, ActiveCell.Sum (myrange) is just the last thing I tried.

Any ideas how I can solve this?

Djerun
  • 37
  • 5

1 Answers1

1

Is this what you want?

You should avoid using Select as far as possible.

Sub more_twelve_months()

Dim myrange As Range
Dim more_twelve_months As Range 'declare your variables

Set myrange = Range(Range("F5"), Range("F5").End(xlToRight))
    
Set more_twelve_months = Range("A1:ZZ10000").Find("x")

If Not more_twelve_months Is Nothing Then 'check you've found something to avoid errors
    more_twelve_months.Offset(1).Value = Application.Sum(myrange)
End If

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
  • 1
    Yes, it does. Thanks a lot. As a follow up, if you don´t mind: I tried to use ```FillDown ``` do copy the formular down the column, but then it copies down the first cell in the column. How would I need to modify the makro to do this to the whole column except the header? – Djerun Jul 09 '20 at 15:17
  • Define the starting cell as row 2? If that's not clear I suggest you start a new question. Thanks for accepting this one. Btw you can add a formula in one go without using FillDown. – SJR Jul 09 '20 at 15:33