The Dutchman
Board Regular
- Joined
- Apr 10, 2008
- Messages
- 72
I had a sumif formula in a range of cells to caluculate the total transactions for a series of respective dates. Because it is an ongoing database I had the sumif range going from rows 7 to 65536. This really affected the speed of my template.
What I am trying to do is replace the static formula range mentioned above and replace it with a dynanic range based on the cells that are actually occupied using code. The user will move to this part of the template by using a commandbutton.
I want to replace the first cell(which currently has a static sumif statement) at the top of the form with a dynamic one. After I have inserted the formula via code I want to copy that formula down the cells below it.
I believe the problem is trying to pass the variable "nextrow" in the formula because it has " " marks around it.
The code I have developed is as follows:
Any ideas?
What I am trying to do is replace the static formula range mentioned above and replace it with a dynanic range based on the cells that are actually occupied using code. The user will move to this part of the template by using a commandbutton.
I want to replace the first cell(which currently has a static sumif statement) at the top of the form with a dynamic one. After I have inserted the formula via code I want to copy that formula down the cells below it.
I believe the problem is trying to pass the variable "nextrow" in the formula because it has " " marks around it.
The code I have developed is as follows:
Code:
nextrow = Range("FA65536").End(xlUp).Row + 1
Worksheets(1).Range("EE7").Formula = _
"=SumIf(Range("$FA$7:$FA$" & nextrow), Range("$EA7"), _
Range("$FA$7:$FH$" & nextrow))"
Worksheet(1).Range("EE7").Copy Range("EE8:EE37")
Any ideas?