Results 1 to 5 of 5

Use of Variable within Formula

This is a discussion on Use of Variable within Formula within the Excel Questions forums, part of the Question Forums category; I am getting a run-time 1004 error with the following formula. I am using the variable 'maxrow' which is defined ...

  1. #1
    Board Regular Sean Stevens's Avatar
    Join Date
    Jul 2003
    Posts
    123

    Default Use of Variable within Formula

    I am getting a run-time 1004 error with the following formula.

    I am using the variable 'maxrow' which is defined as an integer, but there must be a problem with the way I have created the formula.


    Sheets("Finance").Range("K" & maxrow).Formula = "=SUMIF($I$5:$I$ & maxrow,""Internal"",K5:K & maxrow)"

    Can anyone spot the problem??

  2. #2
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,199

    Default Re: Use of Variable within Formula

    Need to close your quotes before & maxrow the 2nd time
    Lift up your eyes, round about, and see

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    64,589

    Default Re: Use of Variable within Formula

    More quotes needed in the formula itself. Try:

    Sheets("Finance").Range("K" & maxrow).Formula = "=SUMIF($I$5:$I$" & maxrow & ",""Internal"",K5:K" & maxrow & ")"

    It's a string so all the known parts should be surrounded by quotes and the variable not.

  4. #4
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default Re: Use of Variable within Formula

    REplace Formula with value

    Like

    Sheets("Finance").Range("K" & maxrow).Value = "=SUMIF($I$5:$I$ & maxrow,""Internal"",K5:K & maxrow)"

    Also dont quote the maxrow or any vaiable that you want the value from

    Lets say MaxRow = 5 if you put ="MaxRow" then it will equal the work maxrow if you put = maxrow it will equal 5

    i.e. when you get to the part "=SUMIF($I$5:$I$ & ... after the last $ you need to close the quotes.

    HTH

    Jacob

  5. #5
    Board Regular Sean Stevens's Avatar
    Join Date
    Jul 2003
    Posts
    123

    Default Re: Use of Variable within Formula

    Cheers guys - both work.

    This will help me out loads, as I want to code a lot of VBA formulas that make use of variables.

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