CODEWITHSUNDEEP
excelvba
Pete Kittinun
Pete Kittinun

Reputation: 603

Cant create custom function using VBA

I'm trying to create VBA code to find the leftmost nonblank cell in a given range.

Function LEFTMOST_NOBLANK(region As range) As Variant
    
    LEFTMOST_NOBLANK = Application.WorksheetFunction.Lookup(2, 1 / (region <> ""), region)
    
End Function

However, when I run this function in the workbook, it always gives #Value error. However, I can call Lookup function properly inside a worksheet (not VBA).

enter image description here

enter image description here

What did I do wrong?

Upvotes: 0

Views: 40

Answers (1)

Josh Bullough
Josh Bullough

Reputation: 1

This might be more than you wanted but here you go.

Function LEFTMOST_NOBLANK(region As Range) As Variant

    Dim arr As Variant
    arr = region.CurrentArray
    

    arr = ReverseArray(arr)
    
    For Each Item In arr
        
        If Item <> "" Then
            LEFTMOST_NOBLANK = Item
            Exit For
        End If
        
    Next Item
    
End Function

''This is only used to flip the values in reverse from what they come in as.
Function ReverseArray(arr As Variant) As Variant
    Dim val As Variant

    With CreateObject("System.Collections.ArrayList") '<-- create a "temporary" array list with late binding
        For Each val In arr '<--| fill arraylist
            .Add val
        Next val
        .Reverse '<--| reverse it
        ReverseArray = .Toarray '<--| write it into an array
    End With
End Function

Upvotes: 0

Related Questions