+ Reply to Thread
Results 1 to 12 of 12

Make large chunk of If statements efficient. Array maybe?

  1. #1
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    Make large chunk of If statements efficient. Array maybe?

    I have created this piece of code and it is not the messiest or long winded piece I have ever made but I need to create more like the one below. This would make it very messy as there would be pages of if statements rather than a few arrays if possible. The code that I have created looks at the value in a cell on one sheet and depending on the value it assigns a cell in a second sheet the value of a cell in a third sheet. Hopefully that is not confusing but here is my code:

    Please Login or Register  to view this content.
    Last edited by mdovey; 04-19-2014 at 07:31 AM.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,464

    Re: Make large chunk of If statements efficient. Array maybe?

    I did not create a file from scratch to try to test this but it compiles. See if this works for you.

    Please Login or Register  to view this content.
    Jeff
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,464

    Re: Make large chunk of If statements efficient. Array maybe?

    By the way your original code does not compile. You need to use

    Please Login or Register  to view this content.
    instead of

    Please Login or Register  to view this content.
    and you are missing "End If".

  4. #4
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Make large chunk of If statements efficient. Array maybe?

    Thanks you for the initial help and the advice as well.

  5. #5
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Make large chunk of If statements efficient. Array maybe?

    Hmm actually it is flagging some of the code as a problem:

    Please Login or Register  to view this content.

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,464

    Re: Make large chunk of If statements efficient. Array maybe?

    With what error message? Compile time or runtime? If runtime, what is the value in C4? Very difficult to diagnose without having your file, attach if possible.

  7. #7
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Make large chunk of If statements efficient. Array maybe?

    Might it be because its part of a range called Buttons? If so Im not sure I will be able to change it or take it out of the the range. Is there anyway to refer to the cell even though it is part of a named range without referring to the whole named range?

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,464

    Re: Make large chunk of If statements efficient. Array maybe?

    I don't understand your question--you are already referring to specific cells by cell address. This is not related to named ranges.

    I can't help unless you can answer the questions I asked above and attach your file.

  9. #9
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Make large chunk of If statements efficient. Array maybe?

    Sorry,
    When I attempt to run the code i am presented with a "424 runtime error Object Required". I was saying before that C4 is parts of a named range called 'buttons' and was wondering if this effected it.

  10. #10
    Registered User
    Join Date
    10-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    78

    Re: Make large chunk of If statements efficient. Array maybe?

    I found the problem is seems that if cell C4 is blank then the code runs into an error. Is there a way of making it so if the cell C4 is blank it returns the first value in the array instead of returning an error?

  11. #11
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,464

    Re: Make large chunk of If statements efficient. Array maybe?

    Add code in red

    Please Login or Register  to view this content.

  12. #12
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Make large chunk of If statements efficient. Array maybe?

    Hi,

    You don't actually need a macro to do this since there is a consistent offset. You could use a normal OFFSET() function.

    Untested but something like

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    in L4 on the Sales Table sheet
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ 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. Replies: 45
    Last Post: 12-19-2013, 10:17 AM
  2. Efficient way to animate changing colors for a large number of shapes?
    By nannerdw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-23-2011, 08:08 AM
  3. Procedure too large, can be more efficient?
    By scubadiver007 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-15-2011, 08:35 AM
  4. How to make VBA code more efficient?
    By globulous in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-14-2010, 12:02 PM
  5. Efficient logic to deal with large files
    By thedude36 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-12-2008, 05:17 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