CODEWITHSUNDEEP
excelvba
jmaz
jmaz

Reputation: 527

Pass Excel Range in VBA Function, Process as Array, and Return Result

I have an Excel worksheet with some strings in a column. Sometimes all of the entries are the same, and sometimes not:

enter image description here

enter image description here

I wrote a function to pass the range as a parameter:

=Dent_WG(A1:A6)

The VBA function should determine which case is true (all entries = "Al", or at least one entry = "Ag"), then return 0 or 12 respectively:

Function DentWG(WG_Mat As Range) As Single

    Dim dat As Variant, rw As Variant, temp As Single
    dat = WG_Mat
    temp = 0
    
    For rw = LBound(dat, 1) To UBound(dat, 1)
        If dat(rw, 1) = "Ag" Then
            temp = 12
        End If
    Next

    If temp = 12 Then
        DentWG = 12
    Else
        DentWG = 0
    End If

End Function

However, the function always returns 0, even for the 2nd case where "Ag" occurs in the range. I'm sure I'm failing to correctly convert the range into an array or correctly apply the intended logic to that array.

Upvotes: 1

Views: 1970

Answers (3)

Alex P
Alex P

Reputation: 12497

This works for me:

Function DentWG(WG_Mat As Range) As Single
    Dim result As Single, cl as Range
    result = 0

    For Each cl In WG_Mat
        If cl = "Ag" Then
            DentWG = 12
            Exit Function
        End If
    Next cl

    DentWG = result
End Function

Upvotes: 2

Siddharth Rout
Siddharth Rout

Reputation: 149335

From your question...

The VBA function should determine which case is true (all entries = "Al", or at least one entry = "Ag"), then return 0 or 12 respectively:

This is what you need.

Function DentWG(WG_Mat As Range) As Long 
    Dim ClCount As Long

    ClCount = WG_Mat.Cells.Count

    If Application.WorksheetFunction.CountIf(WG_Mat, "Al") = ClCount Then
        DentWG = 0
    ElseIf Application.WorksheetFunction.CountIf(WG_Mat, "Ag") > 0 Then
        DentWG = 12
    End If
End Function

The same can be achieved using a formula

=IF(COUNTIF(A1:A6,"Al")=(ROWS(A1:A6)*COLUMNS(A1:A6)),0,IF(COUNTIF(A1:A6,"Ag") > 0‌​,12,""))

In case it will always be 1 Column then you don't need *COLUMNS(A1:A6). This will do.

=IF(COUNTIF(A1:A6,"Al")=ROWS(A1:A6),0,IF(COUNTIF(A1:A6,"Ag") > 0,12,""))

ScreenShot

enter image description here

Upvotes: 2

maybeWeCouldStealAVan
maybeWeCouldStealAVan

Reputation: 15610

You don't really need a UDF for this. You could just say:

=IF(COUNTIF(A1:A6,"Ag")>=1,12,0)

Upvotes: 2

Related Questions