Issue with referencing a Range

SMI// · May 31st, 2017, 02:58 AM

Hi Shahsur and all my other friends,
I have 3 different ranges referenced in CALC_Ranges array. Each range has four rows. Then I have another array CALC_answers having 3 correct answers for each of these 3 ranges.

I have used 2 loops, an outer loop that cycles through all 3 ranges and an inner loop which is there to check all the 4 rows of each of these 3 ranges.

I want to identify which of the 4 rows of the current range of outer loop is being checked for the answer.

Problem 1
=======
My code "If Not IsEmpty(Range(CALC_RANGES(a)).Range("A" & b + 1)) = True" works fine and identifies if the row within the inner loop is blank or not but I am not being able to extract a number between 1, 2, 3 or 4 being the row of that particular range from the outer loop.

Problem 2
=======
One of my 3 named ranges is not in continuity, like this defined range is comprised of non adjacent cells from top to bottom. Here Range(CALC_RANGES(a)).Activate method is working fine but the inner loop to check each of the 4 rows within that range is not looping through non adjacent cells within this range.

Objective
=======
If I am able to identify row ref of inner loop as to which of the 4 rows of a given range is being examined, I will match it with my Answers Array value. For example my Answers array is CALC_ANSWERS = Array(2, 1, 4), so if the user checks "x" row 1,3 or 4 for the first of 3 CALC_ranges, then I will compare it with CALC_Answers array which should be 2 instead of those 3 possible selections. So, my code will Red color code the checked cell. Alternatively, if the answer is correct it will change ColorIndex value to Green as correct answer.

Code:

Sub calc()

Range("i:i").Interior.ColorIndex = xlNone

CALC_RANGES = Array("CALC101", "CALC102", "CALC103")
CALC_ANSWERS = Array(2, 1, 4)


    For a = 0 To 2
        Range(CALC_RANGES(a)).Activate

        For b = 0 To 3
        
            
                     
            If Not IsEmpty(Range(CALC_RANGES(a)).Range("A" & b + 1)) = True Then
                    Application.StatusBar = calc_answers(a)
                        
                If Range(CALC_RANGES(a)).Range("A" & b + 1).Row = calc_answers(a) Then
                    Range(CALC_RANGES(a)).Range("A" & b + 1).Interior.ColorIndex = 4
                    Exit For
    
                Else
                    Range(CALC_RANGES(a)).Range("A" & b + 1).Interior.ColorIndex = 3
                Exit For
                            
                End If
        
            End If
        
          Next b
    
    
    Next a
    


End Sub