PDA

View Full Version : Solved: select correct sheet if complied to 2 values on a selected worksheet



dbekkers
09-14-2012, 12:53 AM
Hello,

I've written the following code. I use it to select the correct sheet based on a value in cell D41 (variable can be 1 or 2) AND a value in cell H122 or H129.
If the value in cell D41 = 1 then he has to look in cell H122 for the next value. If the value in cell D42 = 2 then he has to look in cell H129 for the next value. Based on the combination of the values in cell D42 and H122 or H129 a sertain sheet has to be selected.

It works fine like this but i'm looking for an easier way to achieve the same result. Does anyone have any suggestions?

sheets("parameters").Visible = True
Sheets("parameters").Select

If Range("D41").Value = "1" And Range("H122") = "1" Then GoTo 136
If Range("D41").Value = "1" And Range("H122") = "2" Then GoTo 148
If Range("D41").Value = "1" And Range("H122") = "3" Then GoTo 154
If Range("D41").Value = "1" And Range("H122") = "4" Then GoTo 160
If Range("D41").Value = "2" And Range("H129") = "1" Then GoTo 224
If Range("D41").Value = "2" And Range("H129") = "2" Then GoTo 230
If Range("D41").Value = "2" And Range("H129") = "3" Then GoTo 236
If Range("D41").Value = "2" And Range("H129") = "4" Then GoTo 248
If Range("D41").Value = "2" And Range("H129") = "5" Then GoTo 254

136
Sheets("136").Select
GoTo 999

148
Sheets("148").Select
GoTo 999

154
Sheets("154").Select
GoTo 999

160
Sheets("160").Select
GoTo 999

224
Sheets("224").Select
GoTo 999

230
Sheets("230").Select
GoTo 999

236
Sheets("236").Select
GoTo 999

248
Sheets("248").Select
GoTo 999

254
Sheets("254").Select
GoTo 999
999
Sheets("parameters").Visible = False

BrianMH
09-14-2012, 04:23 AM
If Range("D41").Value = "1" And Range("H122") = "1" Then sheets("136").select
If Range("D41").Value = "1" And Range("H122") = "2" Then sheets("148").select
If Range("D41").Value = "1" And Range("H122") = "3" Then sheets("154").select
If Range("D41").Value = "1" And Range("H122") = "4" Then sheets("160").select
If Range("D41").Value = "2" And Range("H129") = "1" Then sheets("224").select
If Range("D41").Value = "2" And Range("H129") = "2" Then sheets("230").select
If Range("D41").Value = "2" And Range("H129") = "3" Then sheets("236").select
If Range("D41").Value = "2" And Range("H129") = "4" Then sheets("248").select
If Range("D41").Value = "2" And Range("H129") = "5" Then sheets("254").select

dbekkers
09-14-2012, 06:14 AM
Of course! :-P, Thanks!