check if valid and size array

[Guest]

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!!

 

[NickHK]

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

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!!