Consulting

Results 1 to 14 of 14

Thread: Solved: CountIF with VBA

  1. #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?

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    [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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #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.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #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.

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    What are you referring to, my code results, or your worksheet formulae?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

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

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #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

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    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?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    My excel calculates different

  12. #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")

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

    [VBA]
    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[/VBA]

    It works now

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    That isn't working on two fronts. It is perpetuating the error that you have in D413, 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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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