# 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. ## 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. ## Re: Use of Variable within Formula

Need to close your quotes before & maxrow the 2nd time

3. ## 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. ## 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. ## 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.

#### Posting Permissions

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