How to do Multiple If Statements Shortcut!

[genracela]

I have a code that is too long and I want to shorten it.

Is there a way to do it?


[VBA]
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
[/VBA]
Thanks!!

[Ken Puls]

[vba]
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[/vba]

[lynnnow]

Hi genracela,

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

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

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

[Bob Phillips]

[vba]

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
[/vba]

[Ken Puls]

Okay, Bob, now you're just showing off!

[Bob Phillips]

Okay, Bob, now you're just showing off!

Ken, that's what I do

[Bob Phillips]

Okay, Bob, now you're just showing off!

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]

I'm very proud of you for holding off, Bob. You must be mellowing at your advanced age.

[Bob Phillips]

I'm very proud of you for holding off, Bob. You must be mellowing at your advanced age.

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

[austenr]

Some of us are greatful he shows up and shows off!!!

[Simon Lloyd]

Impressive Bob but can you do it whilst reciting the alphabet backwards?

[Bob Phillips]

Impressive Bob but can you do it whilst reciting the alphabet backwards?

Only the Sanskrit alphabet!

[Simon Lloyd]

<---------------off to find the book on "How to out smart a smart Alec!"

[Aussiebear]

<---------------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?