Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Display Modes
  #1 (permalink)  
Old May 31st, 2017, 02:58 AM
Authorized User
Points: 111, Level: 2
Points: 111, Level: 2 Points: 111, Level: 2 Points: 111, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Apr 2009
Posts: 21
Thanks: 1
Thanked 0 Times in 0 Posts
Default 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.

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.

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

        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
                    Range(CALC_RANGES(a)).Range("A" & b + 1).Interior.ColorIndex = 3
                Exit For
                End If
            End If
          Next b
    Next a

End Sub
Reply With Quote

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 03:40 PM.

Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.