Solved: CountIF with VBA

[tmatematikas]

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:

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

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?

[Bob Phillips]

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:D" & i + 2))
End With

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

End Sub

[tmatematikas]

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.

[Bob Phillips]

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.

[tmatematikas]

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.

[Bob Phillips]

What are you referring to, my code results, or your worksheet formulae?

[tmatematikas]

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

[Bob Phillips]

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

[tmatematikas]

That is what I'm looking for :think:. 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 :dunno

[Bob Phillips]

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?

[tmatematikas]

My excel calculates different :(

[tmatematikas]

i've also noticed, that

WorksheetFunction.Var(Range("A1:A500"))
Gives different ansver compared to some cell with formula =Var("A1:A500")

[tmatematikas]

Finally, I've tried VBA recording and get:


Sub Macro1()

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

End Sub

It works now :type

[Bob Phillips]

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



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