Results 1 to 3 of 3

Thread: How to modify custom function. Help

  1. #1

    Question How to modify custom function. Help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all:

    I have a custom function that count a range of cells by a specific color that works great, but i need to modify it, so it doesn't count more than one cell in a row. This is the code:

    Code:
    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult
    lCol = rColor.Interior.ColorIndex
    If SUM = True Then
    For Each rCell In rRange
    If rCell.Interior.ColorIndex = lCol Then
    vResult = WorksheetFunction.SUM(rCell, vResult)
    End If
    Next rCell
    Else
    For Each rCell In rRange
    If rCell.Interior.ColorIndex = lCol Then
    vResult = 1 + vResult
    End If
    Next rCell
    End If
    ColorFunction = vResult
    End Function
    And this is the formula: "=colorfunction(A362,AP357:AV360,FALSE)"
    were the first cell 'A362' is used as color reference.

    For example, using the formula as reference, we will use cell 'A362' that is RED, to count from 'AP357:AV360', that means we have 7 columns and 4 rows, and we have 'AP357, AT357, AQ358 and AV360' marked with RED. So the new Formula should count only 3, because there are 2 marked cells in a same row. I have been trying this for weeks without results. Any help out there will be appreciated. Thanks

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    I suggest instead of for each rCell in rRange you should loop rows and columns
    (untested code below, may be minor typos)

    for iRow = 1 to ubound(lCol,1)
    for jCol = 1 to ubound(lcol,1)
    for k = jCol+1 to ubound(lcol,1) -1
    if lCol(iRow,k) = lcol(iRow,jCol) then lCol(iRow,k) = -1 'flag as already counted
    next k
    next jCol
    next iRow

    Then can continue as normal or include code in upper loop to add

  3. #3
    Quote Originally Posted by WizzardOfOz View Post
    I suggest instead of for each rCell in rRange you should loop rows and columns
    (untested code below, may be minor typos)

    for iRow = 1 to ubound(lCol,1)
    for jCol = 1 to ubound(lcol,1)
    for k = jCol+1 to ubound(lcol,1) -1
    if lCol(iRow,k) = lcol(iRow,jCol) then lCol(iRow,k) = -1 'flag as already counted
    next k
    next jCol
    next iRow

    Then can continue as normal or include code in upper loop to add
    Hi wizzardOfOz:
    Thanks for the reply, i really didn't get it, but a friend on a forum helped with the function and ended with this code
    Code:
    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM AsBoolean) 
        Dim i As Long, ii As Long     For i = 1 To rRange.Rows.Count         For ii = 1 To rRange.Columns.Count             If rRange(i, ii).Interior.ColorIndex = rColor.Interior.ColorIndex Then                 ColorFunction = ColorFunction + IIf(SUM, Val(rRange(i, ii).Value), 1)                 Exit For             End If         Next     Next  End Function

    I share it and hope it help someone else. I know there are a lot of people out there with similar situations. Thanks a lot WizardOfOz, i really appreciate your help bro. Have a nice day

Tags for this Thread

Posting Permissions

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