Use of Variable within Formula

[Sean Stevens]

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

 

[earlyd]

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

 

[Andrew Poulsom]

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.

 

[DRJ]

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

 

[Sean Stevens]

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.