+ Reply to Thread
Results 1 to 6 of 6

Loop through 2 sheets

Hybrid View

  1. #1
    Registered User
    Join Date
    10-12-2009
    Location
    London,England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Loop through 2 sheets

    For I = 1 To 2
    Sheets(I).Select
    For Row = 0 To x
    Z = Application.CountIf(Range,Criterium)
    Sheets("sheet3").Range("B1").Offset(Row, 0).Value = Za = a + 1
    Next
    Next I
    Without the loop for the sheets, it works out fine, and in sheet 3 is shown the results of the CountIf.
    With the loop, all the results are 0.
    Any idea what the problem is?

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Loop through 2 sheets

    You're giving us the minimum information possible. What are you trying to do? What are the variables? Is this line a typo?
    Sheets("sheet3").Range("B1").Offset(Row, 0).Value = Za = a + 1

  3. #3
    Registered User
    Join Date
    10-12-2009
    Location
    London,England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Loop through 2 sheets

    For I = 1 To 2
    Sheets(I).Select
    For Row = 0 To x
    Z = Application.CountIf(Rows(a),"JEF")
    Sheets("sheet3").Range("B1").Offset(Row, 0).Value = Z 
    a + 1
    Next
    Next I
    typo indeed.
    Well, not a lot more of information is required.
    for example, I'm counting all the people named JEF in sheet 1 and 2.
    In sheet 3 you can see the results.
    So, I tried this For Next, to loop through the two sheets, but the results are 0.
    For I = 1 To 2
    Sheets(I).Select
    Next I
    But, if I do it without the loop for worksheets. It works perfectly, the negative point is that my code is twice as long.

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Loop through 2 sheets

    Try referencing the sheet directly
    Z = Application.CountIf(Sheets(I).Rows(a)
    and remove the Select line. I still can't follow your code - you should define your variables.

    Also, since you're asking a question here don't you think we should be the judge of how much information is needed?

  5. #5
    Registered User
    Join Date
    10-12-2009
    Location
    London,England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Loop through 2 sheets

    Sub CountJEF()
    Dim a, x, num As Integer
    a = 1
    
    'Copie the names from sheet1 to sheet3 (where the results are showed)
    Worksheets("sheet1").Select
    Range("A1", Range("A" & Rows.Count)).Copy Worksheets("sheet3").Range("A1")
    
    'Making a loop to count JEF in 1 sheet
    x = Range("A1").CurrentRegion.Rows.Count - 1
    For Row = 0 To x
    num= Application.CountIf(Rows(a), "JEF")
    Sheets("Test2").Range("B1").Offset(Row, 0).Value = Num
    a = a + 1
    Next
    
    End Sub
    This works, the right results are showed
    Sub CountJEF()
    Dim a, x, num As Integer
    a = 1
    
    'Copie the names from sheet1 to sheet3 (where the results are showed)
    Worksheets("sheet1").Select
    Range("A1", Range("A" & Rows.Count)).Copy Worksheets("sheet3").Range("A1")
    
    'Making a loop to count JEF in 2 sheet
    x = Range("A1").CurrentRegion.Rows.Count - 1
    
    For I = 1 to 2
    For Row = 0 To x
    num= Application.CountIf(Sheets(I).Rows(a), "JEF")
    Sheets("Test2").Range("B1").Offset(Row, 0).Value = Num
    a = a + 1
    Next
    Next I
    End Sub
    This doesn't work, all the results are 0

    I hope this is enough information

  6. #6
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Loop through 2 sheets

    Repeatedly posting the same code is not information. Post a workbook. I don't see why you can't use formulae anyway.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1