Create customized count function in VBA

Question

Hi I want to create a function to count the number of values in an array which have specific characteristic and need your help here:

Basically the list of array is like below (cut short a bit alr):

1111111 1111110 1111101 1101001 1011111 1011110 1011101 1011100 1011001

and I have a value in a cell A1 (let's say 1101001)

I want to count the number of values in the array which are larger than the value in cell A1 and the difference between a specific value and cell A1 has sum of digits <= 7

My code is below:

Function NumOps(Curr_ConFig As Variant, ListOfOptions As Range)

    Dim Array1 As Variant
    Dim i As Long
    Dim k As Long
    Dim C As Integer

    Array1 = ListOfOptions
    C = 0

    For i = LBound(Array1) To UBound(Array1)

        k = i - Curr_ConFig

        If k < 0 Then
            C = C
        ElseIf SumDigits(k) > 7 Then
            C = C
        Else: C = C + 1
        End If     
    Next i
    NumOps = C
End Function

Curr_ConFig is supposed to be cell A1. ListOfOptions is supposed to be a Range of the array in Excel.

Assuming I already create a SumDigits() function successfully.

Could someone point me to the right direction? Thanks

If C1:C9 houses the range of array then would it not be simpler to calculate cells larger than A1 as =COUNTIF(C1:C9,">"&A1). Why use UDF? — shrivallabha.redij
– shrivallabha.redij, Commented Nov 9, 2017 at 13:17
The complication is that I want to count only values in the array which have this characteristic: SumDigits(Value in array - A1) <=7 — Dennis Le
– Dennis Le, Commented Nov 9, 2017 at 14:04

Dennis Le · Accepted Answer · 2017-11-12 16:49:56Z

1

The code that works:

Function NumOps(Curr_ConFig As Range, ListOfOptions As Range)

Dim count As Integer
Dim cell As Range
count = 0

For Each cell In ListOfOptions.Cells
    If Val(cell) >= Val(Curr_ConFig) And SumDigits(Val(cell) - Val(Curr_ConFig)) <= 7 Then
        count = count + 1
    End If
Next

NumOps = count

End Function

edited Nov 12, 2017 at 16:49

answered Nov 9, 2017 at 14:50

Dennis Le

131 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

E. Villiger · Accepted Answer · 2017-11-09 11:52:25Z

0

The details of your question are a bit unclear, but this should help:

Function NumOps(Curr_ConFig As Range, ListOfOptions As Range) ' assuming Curr_ConFig is passed in as a Range (Cell)
    Dim count As Integer
    count = 0

    For each cell in ListOfOptions.Cells
        If CInt(cell) > CInt(Curr_Config) And SumDigits(Curr_Config) <= 7 Then ' CInt() converts a string to an integer
            count = count + 1
        End If     
    Next

    NumOps = count
End Function

answered Nov 9, 2017 at 11:52

E. Villiger

92612 silver badges29 bronze badges

3 Comments

E. Villiger Over a year ago

Hint: If you have a range object, most times you can just write the object name to get the value of the cell. In reality, the Property ".Value" is checked in those cases. For example, if you write x = Range("A1") and A1 has a value of "1010110" then x will have the value "1010110". x = Range("A1").Value is strictly equivalent. x would then be of type string. If you want to assign a variable to a Range Object you would use Set x = Range("A1"). I that case, x would be of type Range.

Dennis Le Over a year ago

Thanks for your help. The script is very helpful. I've posted a slightly more refined version which works with real data.

E. Villiger Over a year ago

Glad it helped!

Collectives™ on Stack Overflow

Create customized count function in VBA

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related