Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
read and check cells in loop
Hi! I have problem with a piece of code that I dont understand. It is udf.
It works like this: it takes three cells as argument (although you dont 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 €¦€¦. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
read and check cells in loop
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. "Arne Hegefors" wrote: Hi! I have problem with a piece of code that I dont understand. It is udf. It works like this: it takes three cells as argument (although you dont 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 €¦€¦. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loop to check cells for numbers between 2 number range | Excel Programming | |||
Do Loop for Error check | Excel Programming | |||
check blocks in loop | Excel Programming | |||
having probelms getting my loop to terminate on an array value read from an empty cell | Excel Programming | |||
Create a Loop to check cells between workbooks | Excel Programming |