read and check cells in loop

[Arne Hegefors]

Hi! I have problem with a piece of code that I don’t understand. It is udf.
It works like this: it takes three cells as argument (although you don’t see
that in the code). I then check the contents of those three cells to see if
they match two arrays that you can see in my code. If the content matches the
contents of one of the arrays I redim another array and places a number in
that array. It is imperative that the dimension of that array (A) equals the
number of cells with OK content. This works sometimes but if I have empty
cells that are taken in as argument in the beginning (eg in Column A) then it
does not work. Can someone please help me with this piece of code?

Function basel(Rating As Range) As String

Dim i As Long
Dim j As Long
Dim k 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")

i = 1 'räknare för kolumner
j = 1 'räknare för ratingbetyg
k = 0
For i = 1 To Rating.Columns.Count 'kollar varje kolumn för sig
For j = 1 To UBound(RatingScale) 'kollar varje OK rating
If (Rating(i) = RatingScale(j) Or Rating(i) = MdyRatingScale(j))
Then
k = k + 1
ReDim Preserve A(k) 'dimensionera om A
' MsgBox k
A(i) = j 'de får samma sifferbetyg som i ratingens placering
i arrayen
End If
Next j
Next i

…….

 

[Madhan]

Hi, in my opinion, within the loop, the first check you should perform is, to
verify if a cell is empty, if so, then go to the next iteration in the loop.