Arne,
Depending exactly what you expect as a return value, you can amend this:
Function basel(Rating As Range) As String
Dim cell As Range
Const RatingScale As String = ",AAA,AA+,AA,AA-,A+,A,A-,BBB+,BBB,BBB-,"
Const MdyRatingScale As String = ",Aaa,Aa1,Aa2,Aa3,A1,A2,A3,Baa1,Baa2,Baa3,"
For Each cell In Rating
Debug.Print cell.Text
If InStr(1, RatingScale, "," & cell.Text & ",") = 0 Then
If InStr(1, MdyRatingScale, "," & cell.Text & ",") = 0 Then
basel = "No match: " & cell.Text
Exit Function
End If
End If
Next
basel = "All match"
End Function
Maybe someone more knowledgable in RegEx than me could come with a
Pattern....
NickHK
"Arne Hegefors" <(E-Mail Removed)> wrote in message
news:029BAA8A-D649-4485-8043-(E-Mail Removed)...
> Hi! I have a udf that reads the contents of a certain nnumber of cells. If
> the contents in these cells match the contents of any position in any of
two
> arrays defined by me in my code then contents of that cell is valid and
can
> be processed further.
>
> My code starts like this:
>
> Function basel(Rating As Range) As String
>
> Dim i As Long
> Dim j As Long
> Dim A() As String
> Dim RatingScale As Variant
> Dim MdyRatingScale As Variant
>
> RatingScale = Array("AAA", "AA+", "AA", "AA-", "A+", "A", "A-",
"BBB+",
> "BBB", "BBB-")
> MdyRatingScale = Array("Aaa", "Aa1", "Aa2", "Aa3", "A1", "A2", "A3",
> "Baa1", "Baa2", "Baa3")
>
> The two arrays define what is ok content. I want my code to check if the
> content in the cells that it is taking in as argument is ok. if it is ok
the
> code shall place the content into an array called A. It is important that
> there are no empty places in A so it needs to de Redimmed so that it is
only
> as big as it needs to be. can someone help me with this please! thanks
alot!!
Similar Threads
