VBA Insert Formula with Variable into Cell

[The Dutchman]

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:

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?

 

[dafan]

Make a new string first, then pass that string to the Formula

TempString = "=SumIf(Range(""$FA$7:$FA$" &" & nextrow & "), Range(""$EA7""), _    
          Range(""$FA$7:$FH$" &" & nextrow & "))"
 
Worksheets(1).Range("EE7").Formula = TempString

 

[The Dutchman]

Thx Stephan:

I had to the cell reference quotation marks and all the references of "Range"... but it worked like a charm!

Ray