worksheetfunction.countifs with loop

hi there,

I'm testing out a VBA using application.worksheetfunction.countifs()

Dim n As Integer
Dim result As Double
Dim lastr As Integer
lastr = Worksheets("Sheet1").UsedRange.Rows.Count

For n = 2 To lastr
result = Application.WorksheetFunction.CountIfs(Cells(n, 1), "O", Cells(n, 2), "Yes")
Next n
Worksheets("Sheet1").Range("J1").Value = result

When i hit the button, i keep getting 0.

any ideas?

I'm using excel 2010

thank you

Hi Lifeseeker,
See if this isn't what you really want.

Private Sub OCount_Click()

    Dim n As Integer
    Dim result As Double
    Dim TotalResult As Double
    Dim lastr As Integer
    
    lastr = Worksheets("Sheet1").UsedRange.Rows.Count
    
        For n = 2 To 100
            result = Application.WorksheetFunction.CountIfs(Cells(n, 1), "O", Cells(n, 2), "Yes")
            TotalResult = TotalResult + result
        Next n
        
    Worksheets("Sheet1").Range("J1").Value = TotalResult

End Sub

Your code above only showed the result for the last row, which was zero.

Read http://www.cpearson.com/excel/Debug.htm to learn how to figure these out for yourself. I set a breakpoint in your code and stepped through it line by line. I could also have set an "Add Watch" to the variable result to see how it changed with each loop.

Have you checked that actually entering a formual like that on your worksheet produces a result?, i suspect it doesn't, have a go

Google Me
Find me located here Simon Lloyd and what i'm about Here
The above is NOT a link to a forum so is NOT against Rule 13