Solved

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

Posted on 2014-01-12
3
298 Views
Last Modified: 2014-01-12
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
0
Comment
Question by:rutgermons
  • 2
3 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39774735
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

Open in new window

0
 

Author Comment

by:rutgermons
ID: 39774817
Thanks MS, if I move my sheets within the workbook, would this skew my index order then?
0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39774830
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

Open in new window

0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question