CODEWITHSUNDEEP
arraysvbaexcel
Dennis Le
Dennis Le

Reputation: 13

Create customized count function in VBA

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

Upvotes: 0

Views: 368

Answers (2)

Dennis Le
Dennis Le

Reputation: 13

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

Upvotes: 1

E. Villiger
E. Villiger

Reputation: 916

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

Upvotes: 0

Related Questions