Passing a variable range to a formula

Greetings all

I am having a bit of difficulty with getting a sumproduct function to work in a macro. The range is variable in length, so this is how I have entered the formula:

Range("A" & lrow + 2)= "Total Formula"
Range("C" & lrow +2).formula= _
"=sumproduct((M12:M"& lrow"),(Q12:Q"& lrow"))"

The variable lrow has been defined previously and the formula works when typed in highlighting the ranges, but the macro crashes when it tries to execute the formula line.

I could go back and use a helper column if necessary, but I would really prefer to do it in cell.

Thanks for taking a look at the problem

You can try this (untested)

Range("C" & lrow +2).formula= _
"=sumproduct((M12:M" & lrow & "),(Q12:Q" & lrow & "))"

Thanks Rylo! Thanks Jaslake! That was exactly what was wrong. Quotation marks and Ampersands drive me crazy at times! Thanks again both of you!!

Hi Jacob

Me too

Quotation marks and Ampersands drive me crazy at times

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save