Create VBA function based on user-defined function

Question

Thanks to all friends who helped me on my question how to calculate specific cells in excel

Now, I need help to code that excel function in VBA The function is : =SUM(IFERROR(VALUE(IF(LEN(H27:Q27)=4,IF(ISNUMBER(SEARCH("b",LEFT(H27:Q27,2))),RIGHT(H27:Q27,1),LEFT(H27:Q27,1)),H27:Q27)),0))

Thanks in advance

A more accurate title would be "Create user-defined function based on formula", but it would still be lacking. You should describe what you want to do specifically, not just generally. Also, your question should contain what you have attempted to solve the problem. — Vegard
– Vegard, Commented May 25, 2016 at 10:26
try evaluate("SUM(IFERROR(VALUE(IF(LEN(H27:Q27)=4,IF(ISNUMBER(SEARCH(""b"",LEFT(H27:Q27,2))),RIGHT(H27:Q27,1),LEFT(H27:Q27,1)),H27:Q27)),0))") — Dirk Reichel
– Dirk Reichel, Commented May 25, 2016 at 10:28
Range is not constant @robin I've tried , but getting error as #value! Public Function Rag(ggiRange As Range) As Single Dim rngCell As Range For Each rngCell In ggiRange Evaluate ("SUM(IFERROR(VALUE(IF(LEN(E19:E20)=4,IF(ISNUMBER(SEARCH(""b"",LEFT(E19:E20,2))),RIGHT(E19:E20,1),LEFT(E19:E20,1)),E19:E20)),0))") Rag = Rag + rngCell.Value Next rngCell End Function — Mamed Aliyev
– Mamed Aliyev, Commented May 25, 2016 at 10:42
So you want the function to iterate over some range - any beginning and end point - and then perform the logic? I ask because =LEN(A1:B1) as a normal formula will error (with #VALUE!) but an array formula will work. — Robin Mackenzie
– Robin Mackenzie, Commented May 25, 2016 at 10:45

Mrig · Accepted Answer · 2016-05-25 12:20:19Z

1

Here you go:

Public Function GetTotal(rng As Range) As Long
    Dim tot As Long
    Dim celString As String
    Dim t1String As String, t2String As String

    For Each cel In rng
        If IsNumeric(cel) Then
            tot = tot + cel.Value
        ElseIf Len(cel.Value) = 4 Then
            celString = cel.Value
            t1String = Left(celString, 2)
            If InStr(1, t1String, "b") = 0 Then
                t2String = Left(celString, 1)
            Else
                t2String = Right(celString, 1)
            End If
            tot = tot + t2String
        End If
        Debug.Print tot
    Next
    GetTotal = tot
End Function

You have to give range as input.

See the image below:

answered May 25, 2016 at 12:20

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

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

Comments

Robin Mackenzie · Accepted Answer · 2016-05-25 12:19:10Z

I think this function implements the formula. It's very difficult to test without your original set of data in the cells. Note the function is called from the Foo sub-routine below - so you can pass in a variable range to the function. Hope that helps.

Function DoIt(rng As Range)
    ' VBA implementation for
    '=SUM(IFERROR(VALUE(IF(LEN(H27:Q27)=4,IF(ISNUMBER(SEARCH("b",LEFT(H27:Q27,2))),RIGHT(H27:Q27,1),LEFT(H27:Q27,1)),H27:Q27)),0))

    Dim dblResult As Double
    Dim rngCell As Range
    Dim intLength As Integer
    Dim strFragment1 As String
    Dim strFragment2 As String
    Dim intPos As Integer

    'set result
    dblResult = 0

    'loop for the array formula
    For Each rngCell In rngTarget

        'check value length = 4
        intLength = Len(rngCell.Value)
        If intLength = 4 Then
            'get bit of string and check for 'b' in string
            strFragment1 = Left(rngCell.Value, 2)
            'search for location of b in cell - use InStr for SEARCH
            intPos = InStr(1, strFragment, "b", vbBinaryCompare)
            If intPos <> 0 Then
                'b in fragment
                strFragment2 = Right(rngCell.Value, 1)
            Else
                'b not in fragment
                strFragment2 = Left(rngCell.Value, 1)
            End If

            '2nd fragment should be a number?  use IsNumeric for ISNUMBER and Val for VALUE
            If IsNumeric(strFragment2) Then
                dblResult = dblResult + Val(strResult)
            End If

        Else
            'cell value length <> 4
            'add cell value to result if is numeric - use IsNumeric for ISNUMBER and Val for VALUE
            If IsNumeric(rngCell.Value) Then
                dblResult = dblResult + Val(rngCell.Value)
            End If

        End If
    'next cell
    Next rngCell

    'return sum
    DoIt = dblResult

End Function

Sub Foo()
    Dim rngTarget As Range

    Set rng = Sheet1.Range("H27:Q27")

    Debug.Print DoIt(rng)

End Sub

Collectives™ on Stack Overflow

Create VBA function based on user-defined function

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related