CODEWITHSUNDEEP
excelvbaexcel-formula
Bruno Ranhel
Bruno Ranhel

Reputation: 1

vba code that is in several cells and checks several ranges

Can anyone help I have this formula in several cells, checking several ranges! Is there a way for me to convert this to VBA so that i only have to select one range in each cell?

Thanks for the help.

=IF(COUNTIF(BE95:BE99;"Falhou")>0;"Falhou";IF(COUNTIF(BE95:BE99;"Falhou Condicionamente")>0;"Falhou Condicionamente";IF(COUNTIF(BE95:BE99;"Passou Condicionamente")>0;"Passou Condicionamente";IF(COUNTIF(BE95:BE99;"Passou")>0;"Passou"))))

Upvotes: 0

Views: 76

Answers (2)

Dang D. Khanh
Dang D. Khanh

Reputation: 1471

Perhaps this is what you want, for it to work let's insert a module and put this code into this module.

Then just type following formulas:

= Test (BE95: BE99)

Function test(rng As Range)
Dim str As Variant
    For Each str In Array("Falhou", "Falhou Condicionamente", "Passou Condicionamente", "Passou")
        If Not IsError(Application.Match(str, rng, 0)) Then test = str:Exit for 'if match exist then return value
    Next str
End Function

Upvotes: 1

Variatus
Variatus

Reputation: 14373

The function below has one feature your worksheet function doesn't have: It returns "Falhou" if the range contains none of the 4 count criteria.

Function Passou(Rng As Range) As String

'    '=IF(COUNTIF(BE95:BE99;"Falhou")>0;"Falhou";
'    IF(COUNTIF(BE95:BE99;"Falhou Condicionamente")>0;"Falhou Condicionamente";
'    IF(COUNTIF(BE95:BE99;"Passou Condicionamente")>0;"Passou Condicionamente";
'    IF(COUNTIF(BE95:BE99;"Passou")>0;"Passou"))))

    Dim Sp() As String
    Dim i As Integer

    Sp = Split("Falhou,Falhou Condicionamente,Passou Condicionamente,Passou", ",")
    For i = UBound(Sp) To 1 Step -1
        If Application.CountIf(Rng, Sp(i)) Then Exit For
    Next i
    Passou = Sp(i)
End Function

Call the UDF from the worksheet specifying the range to be searched.

= Passou($BE$95:$BE$99)

Absolute addressing of the range enables copying of the formula across rows and columns.

Upvotes: 0

Related Questions