evaluate question

[Gary Keramidas]

tried both of these sets of formulas, they return the correct result if the right sheet is active. how would i write the formula so it wouldn't matter which sheet is active?
the macro should only be run when this sheet is active, but i wanted to see if the formula could be written to take that into consideration. now i just activate the sheet before the code is run.

misScans = Evaluate("countblank(" & ws.Range("F6:F" & lastrow).Address(0, 0) & ")")
or
misScans = Evaluate("SUMPRODUCT((F6:F11049="""")*(A6:A11049=""""))")

totalScans = Evaluate("SUMPRODUCT((F6:F11049>"""")*(A6:A11049>""""))")
or
totalScans = Evaluate("COUNTA(" & ws.Range("A6:A" & lastrow).Address(0, 0) & ")")

 

[JMB]

try setting the fourth parameter of Address (external) to true:

misScans = Evaluate("countblank(" & ws.Range("F6:F" & lastrow).Address(, , ,
True) & ")")

 

[Gary Keramidas]

thanks, jm. i thought i tried that too, but must have had the wrong combination.