Results 1 to 3 of 3

VBA Insert Formula with Variable into Cell

This is a discussion on VBA Insert Formula with Variable into Cell within the Excel Questions forums, part of the Question Forums category; I had a sumif formula in a range of cells to caluculate the total transactions for a series of respective ...

  1. #1
    Board Regular
    Join Date
    Apr 2008
    Location
    Edmonton, Alberta, Canada
    Posts
    68

    Default VBA Insert Formula with Variable into Cell

    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:
    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?

  2. #2
    Board Regular
    Join Date
    May 2008
    Location
    Netherlands
    Posts
    692

    Default Re: VBA Insert Formula with Variable into Cell

    Make a new string first, then pass that string to the Formula
    Code:
    TempString = "=SumIf(Range(""$FA$7:$FA$" &" & nextrow & "), Range(""$EA7""), _    
              Range(""$FA$7:$FH$" &" & nextrow & "))"
     
    Worksheets(1).Range("EE7").Formula = TempString
    Regards,
    Stefan


    Using Office 2010

  3. #3
    Board Regular
    Join Date
    Apr 2008
    Location
    Edmonton, Alberta, Canada
    Posts
    68

    Default Re: VBA Insert Formula with Variable into Cell

    Thx Stephan:

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

    Ray

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com