Make large chunk of If statements efficient. Array maybe?

**mdovey** · 04-15-2014, 07:36 AM

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

**6StringJazzer** · 04-15-2014, 08:18 AM

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

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

Dim V As Variant
V = Array("", "B14", "F14", "J14", "N14", "R14", "V14", "B28", "F28", "J28", "N28", "R28", "V28")

rws.Range("L4").Value = cws.Range(V(sws.Range("C4").Value)).Value
End Sub

**mdovey** · 04-15-2014, 04:12 PM

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

rws.Range("L4").Value = cws.Range(V(sws.Range("C4").Value)).Value

**6StringJazzer** · 04-20-2014, 11:15 AM

Add code in red

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

Dim V As Variant
V = Array("", "B14", "F14", "J14", "N14", "R14", "V14", "B28", "F28", "J28", "N28", "R28", "V28")

If Range("C4") = "" Then
   rws.Range("L4").Value = cws.Range(V(1)).Value
Else
   rws.Range("L4").Value = cws.Range(V(sws.Range("C4").Value)).Value
End If

End Sub

**6StringJazzer** · 04-15-2014, 08:22 AM

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

ElseIf

instead of

Else 
If

and you are missing "End If".

**mdovey** · 04-15-2014, 04:07 PM

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

**6StringJazzer** · 04-15-2014, 05:54 PM

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.

**mdovey** · 04-15-2014, 06:17 PM

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?

**6StringJazzer** · 04-16-2014, 08:38 AM

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.

**mdovey** · 04-16-2014, 09:07 AM

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.

**mdovey** · 04-18-2014, 04:09 PM

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?

**Richard Buttrey** · 04-20-2014, 11:32 AM

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:

=IF('Order System'!C4<=6,OFFSET('Case'!B14,0,'Order System'!C4-1),OFFSET('Case'!B28,0,'Order System'!C4-7))

in L4 on the Sales Table sheet

Make large chunk of If statements efficient. Array maybe?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Make large chunk of If statements efficient. Array maybe?

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Efficient way to read in text file and account for possibility of large number of rows

Efficient way to animate changing colors for a large number of shapes?

Procedure too large, can be more efficient?

How to make VBA code more efficient?

Efficient logic to deal with large files

Bookmarks

Bookmarks

Posting Permissions