# Thread: Solved: CountIF with VBA

1. ## Solved: CountIF with VBA

Hello everybody. I am modeling some random observations with VBA in Excel. The problem is the use of CountIF in VBA. That function links to data in other sheet and gives different results when is written as follows:

[VBA]Private Sub CommandButton1_Click()

For i = 1 To 10

limit = Range("calculations!B4").Offset(i - 1, 0).Value

' This is incorrect!
freq1 = WorksheetFunction.CountIf(Worksheets("sample").Range("A1:A500"), "<=" & limit)
' This is correct! But I need the previous one freq2 = WorksheetFunction.CountIf(Worksheets("sample").Range("A1:A500"), "<=1.2888")

Range("calculations!E4").Offset(i - 1, 0).Value = freq1
Range("calculations!F4").Offset(i - 1, 0).Value = freq2

Next i

End Sub[/VBA]

The incorect code calculates all observations in the Range. Looks like ("<=" & limit) is not a String containing the restrictions like ("<=1.2888"). In the attached xls the column Excel must be the same as VBA 1. Any ideas?  Reply With Quote

2. [vba]

Private Sub CommandButton1_Click()
Dim i As Long
Dim limit As Double
Dim freq1 As Long, freq2 As Long

For i = 1 To 10

limit = Range("calculations!B4").Offset(i - 1, 0).Value

With Worksheets("sample")

freq1 = Application.CountIf(.Range("A1:A500"), "<=" & limit) - Application.Sum(Range("D3 " & i + 2))
End With

Range("calculations!E4").Offset(i - 1, 0).Value = freq1
Next i

End Sub
[/vba]  Reply With Quote

3. Looks like now results are:

43 500
49 457
61 408
53 347
59 60
53 235
42 182
43 140
40 97
48 57

But second column must do the same actions as the first and we see - numbers are different. I noticed, that those 500 (all observations) are still calculated in each interval.  Reply With Quote

4. No, I got very different results to that.

Frequencies
limits Excel VBA 1 VBA 2

1.2888 43 43
1.5157 49 49
1.6882 61 61
1.8442 53 53
2.0000 59 60
2.1689 53 57
2.3693 42 46
2.6390 43 50
3.1037 40 48
50.2377 48 57

Doesn't agree with your worksheet calculations, but that is because they are wrong.  Reply With Quote

5. Have you tried my code and it worked wrong? Then maybe some add-in or else should be enabled? I have enabled Analysis and Analysis VBA toolkits.  Reply With Quote

6. What are you referring to, my code results, or your worksheet formulae?  Reply With Quote

7. My old (the first i posted) code. Did you also get those 500, 500,...  Reply With Quote

8. I get this with your original code

1.2888 43 43 43
1.5157 49 92 43
1.6882 61 153 43
1.8442 53 206 43
2.0000 59 266 43
2.1689 53 322 43
2.3693 42 364 43
2.6390 43 410 43
3.1037 40 451 43
50.2377 48 500 43  Reply With Quote

9. That is what I'm looking for . Maybe my office installation is damaged, or what? I heard something about links (Tools->references...) but that menu item fails to open (system registry error...). I'm totally lost with no idea   Reply With Quote

10. You have lost me. What is the problem? I posted the correct code for you, it gives the answers I showed in post #4, so what is the issue?  Reply With Quote

11. My excel calculates different   Reply With Quote

12. i've also noticed, that

[vba]WorksheetFunction.Var(Range("A1:A500"))[/vba]
Gives different ansver compared to some cell with formula =Var("A1:A500")  Reply With Quote

13. Finally, I've tried VBA recording and get:

[VBA]
Sub Macro1()

Range("F4").FormulaR1C1 = "=COUNTIF(sample!R[-3]C[-5]:RC[-5],""<=""&calculations!RC[-4])"
Range("F4").AutoFill Destination:=Range("F4:F13"), Type:=xlFillDefault

End Sub[/VBA]

It works now   Reply With Quote

14. That isn't working on two fronts. It is perpetuating the error that you have in D4 13, and you haven't subtracted the previous sum as you did in the other formulae. You want

[vba]

Range("F4").FormulaR1C1 = "=COUNTIF(sample!R1C1:R500C1,""<=""&calculations!RC[-4])-SUM(R3C:R[-1]C)"
Range("F4").AutoFill Destination:=Range("F4:F13"), Type:=xlFillDefault
[/vba]  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•