CODEWITHSUNDEEP
excelvba
Derek
Derek

Reputation: 27

Not quite sure how to write the formula mathematically

So I am a student currently studying VBA. I'm doing ok in the class but still kind of iffy on what how to program. For the question I'm on I have to take numbers in a column and multiply them unless they are less than or equal to zero. Here is an example done by hand of what the result should look like (which are not the same numbers as the actual problem, these are much simpler).

Here is what I have written so far. I'm kinda treating the column as a 1 x 10 array.

Function multpos(C As Variant)
Dim i As Integer
Dim MD As Long
    For i = 1 To 9
        MD = C(1, i) * C(1, i + 1)
    Next i
If C(i, 1) > 0 Then C(i, 1) = C(i, 1) Else C(i, 1) = 1
If C(i + 1, 1) > 0 Then C(i + 1, 1) = C(i + 1, 1) Else C(i + 1, 1) = 1
multpos = MD
End Function

While MD satisfies the equation, it only works for the first two and then doesn't. Intuitively I want to do something like this

     MD = C(1, i) * C(1, i)
Next i

Etc but this is also not mathematically correct. So if I had

MD = C(1, i)

how can I get it to multiply by the next value from here? Feel free to look at my other code and correct me as well since that could just as easily be wrong. Thank you for help in advance.

Upvotes: 2

Views: 44

Answers (2)

user3598756
user3598756

Reputation: 29421

you could exploit AutoFiler() method

Function multpos(C As Range, criteria As String)
    Dim MD As Long
    Dim cell As Range

    With C.Columns(1)
        If IsEmpty(.Cells(1, 1)) Then .Cells(1, 1) = "|header|"
        .AutoFilter Field:=1, Criteria1:=criteria
        If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then
            MD = 1
            For Each cell In C.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants, xlNumbers)
                MD = MD * cell.Value
            Next cell
        End If
        If .Cells(1, 1) = "|header|" Then .Cells(1, 1).ClearContents
        .Parent.AutoFilterMode = False
    End With
    multpos = MD
End Function

to be exploited in your main sub like:

MsgBox multpos(Range("A1:A10"), ">0")

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26660

Something like this should work for you. I tried to comment the code for clarity:

Public Function PRODUCTIF(ByVal vValues As Variant, ByVal sCriteria As String) As Double

    Dim vVal As Variant
    Dim dResult As Double

    'Iterate through vValues and evaluate against the criteria for numeric values only
    For Each vVal In vValues
        If IsNumeric(vVal) Then
            If Evaluate(vVal & sCriteria) = True Then
                'Value is numeric and passed the criteria, multiply it with our other values
                'Note that until a valid value is found, dResult will be 0, so simply set it equal to the first value to avoid a 0 result
                If dResult = 0 Then dResult = vVal Else dResult = dResult * vVal
            End If
        End If
    Next vVal

    'Output result
    PRODUCTIF = dResult

End Function

And you would call the function like this: =PRODUCTIF(A1:A10,">0")

Upvotes: 1

Related Questions