+ Reply to Thread
Results 1 to 6 of 6

Condensing Current Repeating Code by using and Array

  1. #1
    Forum Contributor
    Join Date
    04-23-2014
    Location
    Liverpool...ish
    MS-Off Ver
    Excel 2013
    Posts
    126

    Condensing Current Repeating Code by using and Array

    Would like to know if there is a way to economize this code by use of an array. Essentially there are 11 target cells that trigger a color change of an object based on the value of the cell. Note that the target cells are in two ranges KA6:KA13 and KL6:KL8. The objects are consecutive names “SavLit” 1 to 11
    Trying to sort out various pieces of code in a little project I have been building over the last several months. I have been trying to learn at the same time so now I need to learn how to make my code a bit leaner.

    Please Login or Register  to view this content.
    Thanks as Always
    Scott

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Condensing Current Repeating Code by using and Array

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Condensing Current Repeating Code by using and Array

    See if this helps (untested):
    Please Login or Register  to view this content.
    The different shapes names will depend on values in:
    SaveLit1 for $KA$6
    SaveLit2 for $KA$7
    SaveLit3 for $KA$8
    SaveLit4 for $KA$9
    SaveLit5 for $KA$10
    SaveLit6 for $KA$11
    SaveLit7 for $KA$12
    SaveLit8 for $KA$13
    SaveLit9 for $KL$6
    SaveLit10 for $KL$7
    SaveLit11 for $KL$8

    Hope this helps.

    Best,
    berlan

  4. #4
    Forum Contributor
    Join Date
    04-23-2014
    Location
    Liverpool...ish
    MS-Off Ver
    Excel 2013
    Posts
    126

    Re: Condensing Current Repeating Code by using and Array

    Andy, thank you for your rework. I tried it and it works without problem... and some improvement in load time. I so wish I could think to create coding this way... I look at it and it makes sense but for me to write it out of my head appears to be impossibility... Maybe one day. I do have one question though. Why did you include the Dim Name As String line. Where did it come into play or was it added as a force of habit?

    Thanks Again

    Scott.
    Last edited by TheScott; 05-10-2015 at 02:17 PM.

  5. #5
    Forum Contributor
    Join Date
    04-23-2014
    Location
    Liverpool...ish
    MS-Off Ver
    Excel 2013
    Posts
    126

    Re: Condensing Current Repeating Code by using an Array or Other Code

    Berlan,

    I thank you for your effort as well. While Andy's worked, I did try yours and with a minor error of replacing 'Target' with 'c'. Yours seemed to work with the exception of a 'red' event... for some reason red was never triggered. I will look at it again later to try and see why... It is a learning exercise for me.

    Regards,

    Scott
    Last edited by TheScott; 05-10-2015 at 02:27 PM. Reason: Changeed the Title

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Condensing Current Repeating Code by using and Array

    Yes I add the Name variable with the intention of hold the shape name in it but instead used the With syntax so the Name variable was redundent and I did not remove it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] help changing repeating code into array of somekind also copy highlighted rows to new shee
    By baby_kay_2003 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 10-28-2014, 12:48 PM
  2. Adjusting array arguments; editing current code
    By BDarden in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-10-2014, 02:02 PM
  3. [SOLVED] Condensing VBA Code
    By MHamid in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-30-2013, 03:04 PM
  4. Help condensing a formula using new functions or array
    By Mercanthrope in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-09-2008, 07:44 PM
  5. Removing Blank Cells in an Array and Condensing
    By inwalkedbud in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-10-2007, 11:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1