Need Help Converting This Excel Formula To VBA

[CaliKidd]

I want to replace several worksheet formulas with their VBA equivalents, but I'm having problems with the conversion syntax. If someone could help me convert one of them, then hopefully I'll be able to tackle the others.

Here's an example Excel formula I have in cell B11:

=COUNTIF(INDIRECT("'Sheet 1'!K4:K" & 'Sheet 2'!B54),"Pass")+COUNTIF(INDIRECT("'Sheet 1'!K4:K" & 'Sheet 2'!B54),"Fail")

The desired output should look something like:
Range("B11").Formula = "....."

Also, instead of referring to worksheet tab names "Sheet 1" and "Sheet 2", I would like to refer directly to their VBA sheet property names -- Sheet1 and Sheet2, respectively.

Any help?

 

[villy]

Try:

Sub mssn()
Set ws1 = Sheet1
Set ws2 = Sheet2
With WorksheetFunction
    Range("B11").Formula = .CountIf(ws1.Range("K4:K" & ws2.Range("B54")), "Pass") + _
            .CountIf(ws1.Range("K4:K" & ws2.Range("B54")), "Fail")
End With
End Sub

 

[CaliKidd]

Thanks, Villy. Cheers!

I tried about 30-40 formula variations (unsuccessfully) before I gave up and posted this. I appreciate your help because none of my ideas were on the right track.

 

[villy]

OK cheers!