Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
check if valid and size array
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
check if valid and size array
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" wrote in message ... 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to check valid Date value? | Excel Worksheet Functions | |||
What's the most efficient to check a file name is valid? | Excel Programming | |||
Check if date is valid | Excel Programming | |||
To to check whether a string is a valid reference | Excel Programming | |||
check for valid file | Excel Programming |