View Full Version : Solved: CountIF with VBA

tmatematikas

11-18-2008, 09:54 AM

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?

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

11-18-2008, 10:59 AM

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.

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

11-18-2008, 11:12 AM

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.

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

tmatematikas

11-18-2008, 11:15 AM

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

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

11-18-2008, 11:28 AM

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

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

11-18-2008, 12:01 PM

My excel calculates different :(

tmatematikas

11-18-2008, 12:06 PM

i've also noticed, that

WorksheetFunction.Var(Range("A1:A500"))

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

tmatematikas

11-18-2008, 12:45 PM

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

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.