Wrox Programmer Forums Issue with referencing a Range
 |
 Excel VBA Discuss using VBA for Excel programming.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com

May 31st, 2017, 02:58 AM
 SMI// Authorized User Join Date: Apr 2009 Posts: 23 Thanks: 1 Thanked 0 Times in 0 Posts
Issue with referencing a Range

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")

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

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

 Similar Threads Thread Thread Starter Forum Replies Last Post Referencing controls stefankemp BOOK: Professional Access 2013 Programming 2 July 28th, 2014 03:53 PM Chapter 10: Referencing CSS issue tomche BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 3 April 26th, 2009 05:54 AM referencing html... Jayahar ASP.NET 3.5 Basics 1 September 2nd, 2008 10:41 PM Referencing new node in IE roccosiffredi Ajax 2 May 4th, 2007 03:30 AM Referencing a subform scandalous Access 2 March 16th, 2007 07:05 AM