Consulting

Results 1 to 14 of 14

Thread: Loop problem

  1. #1

    Loop problem

    Hello. I have some trouble to get working my loop. Here is the code:

    If frmStart.multiPageD.Pages(0).optButton.Value = True Then

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    For a = 1 To LastRow - 1
    If frmStart.multiPageD.Pages(0).frameF.Controls("CheckBox" & a).Value = True Then
    checked = frmStart.multiPageD.Pages(0).frameF.Controls("CheckBox" & a).Caption

    ActiveWorkbook.Sheets("1").Activate
    Range("A1").Select
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For b = 1 To LastRow
    ActiveWorkbook.Sheets("1").Activate

    If Cells(b, "A").Value = checked Then

    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    For c = 1 To LastCol
    ActiveWorkbook.Sheets("1").Activate
    If Cells(b, c).Value = "1" Then
    ast = Cells("1", c).Value
    ActiveWorkbook.Sheets("2").Activate
    Range("A1").Select
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

    For d = 1 To LastCol
    If Cells("1", d).Value = ast Then '<<<< THIS WORKS ONLY ONE TIME. 2nd, 3rd... loop doesn't work
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    For e = 1 To LastRow
    If Cells(e, d).Value = 1 Or Cells(e + 1, d).Value = 1 Or Cells(e + 2, d).Value = 1 Then

    m = Cells(e, "A").Value
    ActiveWorkbook.Sheets("3").Activate
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For f = 1 To LastRow
    ActiveWorkbook.Sheets("3").Activate
    If Cells(f, "A").Value = m Then
    For g = 1 To LastCol
    If Cells(f, g).Value = 1 Then
    QS = Cells("1", g).Value
    ActiveWorkbook.Sheets("QSs").Activate
    Range("A1").Select
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For h = 1 To LastRow
    If Cells(h, "A").Value = QS Then
    '<<<<<<<<< FIND ROWS UNTIL A=STOP, COPY TO NEW WORKBOOK. HELP ON THIS ???

    End If
    Next h
    End If
    Next g
    End If
    Next f
    End If
    Next e
    End If
    Next d
    End If
    Next c
    End If
    Next b

    Please help me! It makes me mad!!!!!

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Sephir,
    Can you post your workbook?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Some clues would help. Does it fail, if so where? Dopes it do some but not all, if so what? etc. etc.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    Quote Originally Posted by mdmackillop
    Hi Sephir,
    Can you post your workbook?
    Can't post workbook, sorry. It is my school's work workbook and I can't publish it yet.

  5. #5
    Quote Originally Posted by xld
    Some clues would help. Does it fail, if so where? Dopes it do some but not all, if so what? etc. etc.
    It is doing only one loop, and then stops. It looks like it doesn't store another ast value or something...

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    There are certainly some odd things going on in there.

    You keep recalculating LastCol and LastRow, but alwasy the same way. Why?

    You seem to be loppong all columns within all rows within all columns within all rows within ... Again, why?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    By my calcs, if you had just 20 rows and 5 columns, the innermost loop is exercised 20,000,000 times!
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    Quote Originally Posted by xld
    There are certainly some odd things going on in there.

    You keep recalculating LastCol and LastRow, but alwasy the same way. Why?
    I try to get LastCol and LastRow for every sheet.

    Quote Originally Posted by xld
    You seem to be loppong all columns within all rows within all columns within all rows within ... Again, why?
    ? I don't understand your question. I try to activate sheet 1, loop through all columns and rows to find specific value, then activate sheet 2 and based on that value search again all columns and rows, and so on...

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Then your loops should be inline not nested

    For Sheet 1
    For Each Row
    For Each Column
    Next Column
    Next Row

    For Sheet 2
    For Each Row
    For Each Column
    Next Column
    Next Row

    etc

    not

    For Sheet 1
    For Each Row1
    For Each Column1
    For Sheet 2
    For Each Row
    For Each Column
    Next Column
    Next Row
    Next Column1
    Next Row1
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    Quote Originally Posted by xld
    Then your loops should be inline not nested

    For Sheet 1
    For Each Row
    For Each Column
    Next Column
    Next Row

    For Sheet 2
    For Each Row
    For Each Column
    Next Column
    Next Row

    etc

    not

    For Sheet 1
    For Each Row1
    For Each Column1
    For Sheet 2
    For Each Row
    For Each Column
    Next Column
    Next Row
    Next Column1
    Next Row1
    Hmm... OK. Thank You. And when to close these fors? After each sheet? Should I left it in one sub, or divide it into several subs?

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I exampled that above. I would get it working it one procedure first.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    Quote Originally Posted by xld
    I exampled that above. I would get it working it one procedure first.
    Thank You. What about my 2nd question? >FIND ROWS UNTIL A=STOP, COPY TO NEW WORKBOOK. HELP ON THIS ???< Any way to use it without find command?

  13. #13
    Excuse my ignorance, but will it start from sheet1 after finishing. I mean I need to search these sheets again and again until last cell and for every finded value in sheet1 I want to do all process again and again...

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I think we (at least me) are in a desperate need of an explanation as to what your requirements/objectives are,a s I have no idea, so cannot usefully make any further comments.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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