PDA

View Full Version : Loop problem

Sephir
07-04-2007, 01:06 PM
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 ':banghead::banghead:<<<< 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
':dunno:dunno<<<<<<<<< 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

mdmackillop
07-04-2007, 01:12 PM
Hi Sephir,

xld
07-04-2007, 01:14 PM
Some clues would help. Does it fail, if so where? Dopes it do some but not all, if so what? etc. etc.

Sephir
07-04-2007, 01:17 PM
Hi Sephir,

Can't post workbook, sorry. It is my school's work workbook and I can't publish it yet.

Sephir
07-04-2007, 01:19 PM
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...

xld
07-04-2007, 01:43 PM
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?

xld
07-04-2007, 01:47 PM
By my calcs, if you had just 20 rows and 5 columns, the innermost loop is exercised 20,000,000 times!

Sephir
07-04-2007, 01:48 PM
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.

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...

xld
07-04-2007, 02:57 PM
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

Sephir
07-04-2007, 03:22 PM
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?

xld
07-04-2007, 03:38 PM
I exampled that above. I would get it working it one procedure first.

Sephir
07-04-2007, 03:48 PM
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?

Sephir
07-04-2007, 07:54 PM
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...

xld
07-05-2007, 01:03 AM
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.