running for loop for different sheets based on value in other sheet

Folks

Below is my code and this calls up values in sheet 8

For i = 2 To Sheet8.UsedRange.Rows.Count
val1 = Range("A" & i).Value
val2 = Range("B" & i).Value

How do I change the specifc loop to select different sheets based on another sheet value, i.e. like so:

If Sheet1.Range("F1") = "5" Then
For i = 2 To Sheet8.UsedRange.Rows.Count
ElseIf Sheet1.Range("F1") = "9" Then
For i = 2 To Sheet9.UsedRange.Rows.Count
ElseIf Sheet1.Range("F1") = "O" Then
For i = 2 To Sheet10.UsedRange.Rows.Count
End If

all help will do
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Try this:

``````Sub Demo()

Dim intSheetIndex As Integer

Select Case Sheet1.Range("F1")
Case Is = "5"
intSheetIndex = 8
Case Is = "9"
intSheetIndex = 9
Case Is = "O"
intSheetIndex = 10
End Select

For i = 2 To Sheets(intSheetIndex).UsedRange.Rows.Count
val1 = Range("A" & i).Value
val2 = Range("B" & i).Value
Next

End Sub
``````
Author Commented:
Thanks MS, if I move my sheets within the workbook, would this skew my index order then?
Commented:
Yes it will, if you want you can adjust it to use the worksheet names.

``````Sub Demo()

Dim strSheetName As String

Select Case Sheet1.Range("F1")
Case Is = "5"
strSheetName = "Sheet8"
Case Is = "9"
strSheetName = "Sheet9"
Case Is = "O"
strSheetName = "Sheet10"
End Select

For i = 2 To Sheets(strSheetName).UsedRange.Rows.Count
val1 = Range("A" & i).Value
val2 = Range("B" & i).Value
Next

End Sub
``````

Experts Exchange Solution brought to you by