+ Reply to Thread
Results 1 to 4 of 4

Modification of the function CountIfs VBA

Hybrid View

  1. #1
    Registered User
    Join Date
    04-23-2014
    Location
    Cracow, Poland
    MS-Off Ver
    Excel 2013
    Posts
    2

    Modification of the function CountIfs VBA

    Hello everyone,
    I have the following problem:
    I have multiple answers test and I have answers for two questions for some number of people. This produce two tables (two ranges). I would like to count all people that have the same answers for both questions. I tried function CountIfs but this function seems to work ok just for 1 dimension ranges (not tables). So I create the macro:
    Function LiczWarunki(Zakr1 As Range, Str1 As Variant, Zakr2 As Range, Str2 As Variant) As Variant
    Dim Element1 As Variant
    Dim Element2 As Variant
    Dim i, l As Integer
    Dim FoundMatch1, FoundMatch2 As Boolean
    Dim Licznik As Integer
    Licznik = 0
    l = Zakr1.Rows.Count
    For i = 1 To l
        For Each Element1 In Zakr1.Rows(i)
        FoundMatch1 = False
            If Element1 = Str1 Then
                FoundMatch1 = True
                Exit For
            End If
        Next Element1
        For Each Element2 In Zakr2.Rows(i)
        FoundMatch2 = False
            If Element2 = Str2 Then
                FoundMatch2 = True
                Exit For
            End If
        Next Element2
        If FoundMatch1 = True And FoundMatch2 = True Then
        Licznik = Licznik + 1
        End If
    Next i
    LiczWarunki = Licznik
    End Function
    I don't know why I get the #ARG! after using the macro (for example: =LiczWarunki(E2:M26;"posibility2forquestion1";N2:S26;"posibility5forquestion2").
    Do you see any mistakes?
    Thank you for help.
    Last edited by Leith Ross; 04-23-2014 at 06:41 PM. Reason: Added Code Tags

  2. #2
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Modification of the function CountIfs VBA

    try to replace ; with ,

    =LiczWarunki(E2:M26,"posibility2forquestion1",N2:S26,"posibility5forquestion2"
    Click on the star if you think I helped you

  3. #3
    Registered User
    Join Date
    04-23-2014
    Location
    Cracow, Poland
    MS-Off Ver
    Excel 2013
    Posts
    2

    Re: Modification of the function CountIfs VBA

    Thanks adyteo but this was a formuala in a cell in a excel so there should be ;.
    I solved problem by declaring two new integer variables
    Dim m As Integer
    Dim n As Integer
    m = Zakr1.Columns.Count
    n = Zakr2.Columns.Count
    and replacing Zakr1.Rows(i) by
    Range(Zakr1.Cells(i, 1), Zakr1.Cells(i, 1).Offset(0, m))
    and Zakr2.Rows(i) by
    Range(Zakr2.Cells(i, 1), Zakr2.Cells(i, 1).Offset(0, n))
    You can close thread. Thank you.

  4. #4
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Modification of the function CountIfs VBA

    glad you solved the problem, even better you did it yourself.
    actually, you need to mark the thread SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. FileName / Path Function Modification
    By Richard_Thornton in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-21-2014, 03:52 AM
  2. [SOLVED] I want a little bit of modification to normal VLOOKUP function.
    By Thor Almighty in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-08-2013, 01:05 AM
  3. Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  4. Modification to COUNTIFS formula
    By rhudgins in forum Excel General
    Replies: 17
    Last Post: 06-16-2011, 12:14 PM
  5. countifs function
    By doowop5000 in forum Excel General
    Replies: 3
    Last Post: 06-18-2010, 04:13 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1