Results 1 to 12 of 12

Make large chunk of If statements efficient. Array maybe?

Threaded View

  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:

    Sub Recording()
    Dim lngWriteRow As Long
    Dim sws As Worksheet
    Dim rws As Worksheet
    Set sws = Worksheets("Order System")
    Set rws = Worksheets("Sales Table")
    Set cws = Worksheets("Case")
    
    If sws.Range("C4").Value = "1" Then
    rws.Range("L4").Value = cws.Range("B14").Value
    Else
    If sws.Range("C4").Value = "2" Then
    rws.Range("L4").Value = cws.Range("F14").Value
    Else
    If sws.Range("C4").Value = "3" Then
    rws.Range("L4").Value = cws.Range("J14").Value
    Else
    If sws.Range("C4").Value = "4" Then
    rws.Range("L4").Value = cws.Range("N14").Value
    Else
    If sws.Range("C4").Value = "5" Then
    rws.Range("L4").Value = cws.Range("R14").Value
    Else
    If sws.Range("C4").Value = "6" Then
    rws.Range("L4").Value = cws.Range("V14").Value
    Else
    If sws.Range("C4").Value = "7" Then
    rws.Range("L4").Value = cws.Range("B28").Value
    Else
    If sws.Range("C4").Value = "8" Then
    rws.Range("L4").Value = cws.Range("F28").Value
    Else
    If sws.Range("C4").Value = "9" Then
    rws.Range("L4").Value = cws.Range("J28").Value
    Else
    If sws.Range("C4").Value = "10" Then
    rws.Range("L4").Value = cws.Range("N28").Value
    Else
    If sws.Range("C4").Value = "11" Then
    rws.Range("L4").Value = cws.Range("R28").Value
    Else
    If sws.Range("C4").Value = "12" Then
    rws.Range("L4").Value = cws.Range("V28").Value
    End Sub
    Last edited by mdovey; 04-19-2014 at 07:31 AM.

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