How can I fix this function to search all capitalised words in a string against a named range?

[Magnatolia]

Hi all,

I am trying to build a function that I can use to search a selected cell's string and check each capital word (e.g. ART, RESULT) and check against an approved abbreviation list. It will then return the result of disallowed capitalised words in the output. This is the first stage. I then want to include an exclusion of certain capitalised words such as CONTRACTS and CPO.

Function find_cap_words(Rng As Range)

Dim allowed_abb As Integer
Dim disallowed_abb As Integer
Dim txt
Dim desc() As String
Dim found As Boolean: found = False


desc = Split(Rng.Value, , " ")


For Each txt In desc
MsgBox txt
If (UCase(txt) = txt) Then


    If Range("Abbreviations").Find(txt, , Excel.xlValues) Is Nothing Then
        disallowed_abb = disallowed_abb + 1
        found = False
        Else
        allowed_abb = allowed_abb + 1
        found = True
        
    End If
    End If
    Next


    ActiveCell.Value = "Err: There is/are " & disallowed_abb & " unapproved capitalised words. Review required"
    
        
End Function

I'm getting a value error. I get the correct result through the sub macro.

Any assistance is appreciated.

Thanks!

 

[dmt32]

Hi,
xlvalues is a Contstant and has value of -4163

Try changing code as follows

If Range("Abbreviations").Find(txt, , xlValues) Is Nothing Then

also, as you are passing the range as an argument you should be able to use the declared rng parameter & not need to repeat the range in your function

If Rng.Find(txt, , Excel.xlValues) Is Nothing Then

Dave