PDA

View Full Version : How to do Multiple If Statements Shortcut!



genracela
08-25-2010, 09:48 PM
I have a code that is too long and I want to shorten it.

Is there a way to do it?



Private Sub CommandButton1_Click()
If Range("E6").Value = "198818" Then
Range("C28").Select
Else
If Range("E6").Value = "198819" Then
Range("C39").Select
Else
If Range("E6").Value = "198820" Then
Range("C52").Select
Else
If Range("E6").Value = "198821" Then
Range("C65").Select
Else
If Range("E6").Value = "198822" Then
Range("C78").Select
Else
If Range("E6").Value = "198823" Then
Range("C91").Select
Else
If Range("E6").Value = "198824" Then
Range("C100").Select
Else
If Range("E6").Value = "198826" Then
Range("C109").Select
Else
If Range("E6").Value = "198960" Then
Range("C121").Select
Else
If Range("E6").Value = "198961" Then
Range("C134").Select
Else
If Range("E6").Value = "198962" Then
Range("C147").Select
Else
If Range("E6").Value = "198963" Then
Range("C160").Select
Else
If Range("E6").Value = "200141" Then
Range("C173").Select
Else
If Range("E6").Value = "200142" Then
Range("C186").Select
Else
If Range("E6").Value = "200143" Then
Range("C199").Select
Else
If Range("E6").Value = "200144" Then
Range("C212").Select
Else
If Range("E6").Value = "200145" Then
Range("C225").Select
Else
If Range("E6").Value = "200146" Then
Range("C238").Select
Else
If Range("E6").Value = "200147" Then
Range("C251").Select
Else
If Range("E6").Value = "200148" Then
Range("C264").Select
Else
If Range("E6").Value = "200149" Then
Range("C277").Select
Else
If Range("E6").Value = "225129" Then
Range("C290").Select
Else
If Range("E6").Value = "225131" Then
Range("C303").Select
Else
If Range("E6").Value = "225132" Then
Range("C316").Select
Else
If Range("E6").Value = "234394" Then
Range("C329").Select
Else
If Range("E6").Value = "281113" Then
Range("C342").Select
Else
If Range("E6").Value = "281114" Then
Range("C355").Select
Else
If Range("E6").Value = "281115" Then
Range("C368").Select
Else
If Range("E6").Value = "281116" Then
Range("C381").Select
Else
If Range("E6").Value = "281117" Then
Range("C394").Select
Else
If Range("E6").Value = "281118" Then
Range("C407").Select
Else
If Range("E6").Value = "293587" Then
Range("C420").Select
Else
If Range("E6").Value = "323486" Then
Range("C433").Select
Else
If Range("E6").Value = "357867" Then
Range("C446").Select
Else
If Range("E6").Value = "359228" Then
Range("C459").Select
Else
If Range("E6").Value = "364957" Then
Range("C472").Select
Else
If Range("E6").Value = "373653" Then
Range("C485").Select
Else
If Range("E6").Value = "373654" Then
Range("C498").Select
Else
If Range("E6").Value = "373655" Then
Range("C511").Select
Else
If Range("E6").Value = "373656" Then
Range("C524").Select
Else
If Range("E6").Value = "373657" Then
Range("C537").Select
Else
If Range("E6").Value = "374175" Then
Range("C550").Select
Else
If Range("E6").Value = "374183" Then
Range("C563").Select
Else
If Range("E6").Value = "394279" Then
Range("C576").Select
Else
If Range("E6").Value = "402964" Then
Range("C589").Select
Else

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

Thanks!!

Ken Puls
08-25-2010, 11:39 PM
Select Case Range("E6").Value
Case Is = "198818"
Range("C28").Select
Case Is = "198819"
Range("C39").Select
Case Is = "198820"
Range("C52").Select
'Etcetera

End Select

lynnnow
08-26-2010, 02:01 AM
Hi genracela,

If 198818 is your base number, the you can try this (considering you are always jumping 13 rows):

'If Range("E6") = 198818 Then
'Range("C28").Select
'Else
'Cells(((Range("E6").Value - 198818 + 2) * 13), 3).Select
'End If


Just realized that you don't always jump 13 rows and also the numbers in E6 are not sequentially numbered.

Bob Phillips
08-26-2010, 02:42 AM
Dim aryKeys As Variant
Dim aryCells As Variant
Dim idx As Long

aryKeys = Array(198818, 198819, 198820, 198821, 198822, 198823, 198824, 198826, 198960, _
198961, 198962, 198963, 200141, 200142, 200143, 200144, 200145, 200146, _
200147, 200148, 200149, 225129, 225131, 225132, 234394, 281113, 281114, _
281115, 281116, 281117, 281118, 293587, 323486, 357867, 359228, 364957, _
373653, 373654, 373655, 373656, 373657, 374175, 374183, 394279, 402964)
aryCells = Array("C28", "C39", "C52", "C65", "C78", "C91", "C100", "C109", "C121", _
"C134", "C147", "C160", "C173", "C186", "C199", "C212", "C225", "C238", _
"C251", "C264", "C277", "C290", "C303", "C316", "C329", "C342", "C355", _
"C368", "C381", "C394", "C407", "C420", "C433", "C446", "C459", "C472", _
"C485", "C498", "C511", "C524", "C537", "C550", "C563", "C576", "C589")

On Error Resume Next
idx = Application.Match(Range("E6").Value, aryKeys, 0)
On Error GoTo 0
If idx > 0 Then

Range(aryCells(idx - 1)).Select
End If

Ken Puls
08-26-2010, 08:23 AM
Okay, Bob, now you're just showing off! :p

Bob Phillips
08-26-2010, 08:44 AM
Okay, Bob, now you're just showing off! :p

Ken, that's what I do :dunno

Bob Phillips
08-26-2010, 08:47 AM
Okay, Bob, now you're just showing off! :p

What I could have done was to create a Key class with an ID and Cell property, and a collection Keys class, load the Keys class at start and have a Match method to return the cell if relevant. Now, that would have been showing off!

Ken Puls
08-26-2010, 09:31 AM
I'm very proud of you for holding off, Bob. You must be mellowing at your advanced age. :devil2:

Bob Phillips
08-26-2010, 09:37 AM
I'm very proud of you for holding off, Bob. You must be mellowing at your advanced age. :devil2:

How dare you ... accusing me of mellowing!!!!!!!!!

austenr
08-26-2010, 09:59 AM
Some of us are greatful he shows up and shows off!!!:bow:

Simon Lloyd
08-26-2010, 11:52 AM
Impressive Bob but can you do it whilst reciting the alphabet backwards? :devil2:

Bob Phillips
08-26-2010, 12:06 PM
Impressive Bob but can you do it whilst reciting the alphabet backwards? :devil2:

Only the Sanskrit alphabet!

Simon Lloyd
08-26-2010, 06:10 PM
<---------------off to find the book on "How to out smart a smart Alec!"

Aussiebear
08-27-2010, 03:11 AM
<---------------off to find the book on "How to out smart a smart Alec!"

Bob has written the revised version, of the very book you are seeking. Are you sure you want to go there?