Including LastRow within a formula

[SimonGeoghegan]

Hi All,

I have some code which creates a "score" line within my spreadsheet. I need this formula to change each time I run my macro, as the number of questions it relates to is variable.

I have the formula:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Range("B" & LastRow + 3).Value = "=SUM(COUNTIF(B$8:B$30,""Yes"")/SUM(COUNTA($A$8:$A$30)-COUNTIF(B$8:B$30,""N/A"")))"[/FONT]

Essentially, the "B$30","$A$30" both need replacing to the value of the last row instead of being a fixed value.

Can anyone advise how I'd go about this? I know that my code is just placing a pre-determined text at the minute so expecting I'll need to re-do the entire formula in a different way rather than simply replacing the above references.

Regards,
Simon

 

[Fluff]

How about

Range("B" & LastRow + 3).Value = "=SUM(COUNTIF(B$8:B$" & LastRow & ",""Yes"")/SUM(COUNTA($A$8:$A$" & LastRow & ")-COUNTIF(B$8:B$" & LastRow & ",""N/A"")))"

or

Range("B" & LastRow + 3).FormulaR1C1 = "=SUM(COUNTIF(r8c2:r[-3]c2,""Yes"")/SUM(COUNTA(r8c1:r[-3]c1)-COUNTIF(r8c2:r[-3]c2,""N/A"")))"

 

[SimonGeoghegan]

Thank you Fluff, that is perfect!

 

[Fluff]

You're welcome & thanks for the feedback