PDA

View Full Version : Check for a condition in a Do Unil Loop



rodtt
07-24-2013, 09:10 AM
Hi to all!

I made this code but it's not working as desired.

y = 4
Dim matr As Long
matr = Sheets("Referencia").Range("B5")
Do Until Sheets("20% 1995").Cells(y, 1) = matr
y = y + 1
Loop
Sheets("Relatorio").Range("A2") = "20% Concurso 1995"
Sheets("Relatorio").Range("E2") = Sheets("20% 1995").Cells(y, 30)
I want those two last lines to run only if "matr" variable is found inside "Sheets("20% 1995").Cells(y, 1)"

Thanks in advance!

Paul_Hossler
07-24-2013, 09:24 AM
'Not working' is a little ambigious, but as a guess ....



Dim matr As Long, y As Long
matr = Sheets("Referencia").Range("B5")

y = 4

Do Until (Sheets("20% 1995").Cells(y, 1).Value = matr) Or (y = Sheets("20% 1995").Rows.Count)
y = y + 1
Loop

If y < Sheets("20% 1995").Rows.Count Then
Sheets("Relatorio").Range("A2") = "20% Concurso 1995"
Sheets("Relatorio").Range("E2") = Sheets("20% 1995").Cells(y, 30)
End If


Paul

rodtt
07-24-2013, 09:51 AM
Thanks, Paul!

By "not working" I meant that it was running last two lines everytime, when I wanted it to run only when meating this requirement I posted.

I think your answer is on the right track, but I also think I'm having a problem because my do until loop doesn't have a proper maximum row stated for it to run into.

Paul_Hossler
07-24-2013, 10:07 AM
Do Until (Sheets("20% 1995").Cells(y, 1).Value = matr) Or (y = Sheets("20% 1995").Rows.Count)


this starts at row = 4 and goes until it finds matr OR y reachs the number of rows on the spreadsheet (could take a while)



If y < Sheets("20% 1995").Rows.Count Then


This just tests to see if y is less than max number of rows

so if you found matr before reaching the end of the sheet, then y should should be the row number, otherwise the 2 lines should not execute

without seeing how your data is arranged, it's hard to suggest faster approaches

Paul

rodtt
07-24-2013, 10:24 AM
Do Until (Sheets("20% 1995").Cells(y, 1).Value = matr) Or (y = Sheets("20% 1995").Rows.Count)


this starts at row = 4 and goes until it finds matr OR y reachs the number of rows on the spreadsheet (could take a while)



If y < Sheets("20% 1995").Rows.Count Then


This just tests to see if y is less than max number of rows

so if you found matr before reaching the end of the sheet, then y should should be the row number, otherwise the 2 lines should not execute

without seeing how your data is arranged, it's hard to suggest faster approaches

Paul

Yes, I understood that, but look what is happening:

A) If matr is found: It works as expected
B) If matr is ""(null): It stops when reaching a ""(null) value on sheet "20% 1995" and executes those two lines (when it should not)
C) If matr is a value that does not exists on sheet "20% 1995": It runs for quite a longer time and then I get an error (application-defined or object-defined error).

That's why i think I need to define a maximum row (or do something to end my sheet "20% 1995" on an specific row")

mikerickson
07-24-2013, 11:40 AM
The lines are outside of the Do loop, so they will run, regardless of the status of the looping condition.

The code inside the If..End If will run only if matr is found in column B.


With Sheets("Refernecia").Range("B:B")
If Not (.Find(matr, .after.Cells(1, 1)) Is Nothing) Then
With (.Find(matr, .after.Cells(1, 1))
Sheets("Relatorio").Range("A2") = "20% Concurso 1995"
Sheets("Relatorio").Range("E2") = Sheets("20% 1995").Cells(.Row, 30)
End With
End If
End With

SamT
07-24-2013, 11:48 AM
The last two lines are outside the loop so they run every time the loop ends.

y = Sheets("20% 1995").Rows.Count is the same as y = bottom of sheet. So Y is always less than Rows.Count.

This sub only looks at the used cells in Column "A". IF it ever finds matr, it runs the (not anymore) last two lines then stops.


Sub SamT()
Dim matr As Long, Lr As Long '"Lr" = "LastRow"
Dim Cel As Range

matr = Sheets("Referencia").Range("B5")
Lr = Sheets("20% 1995").Cells(Rows.Count, 1).End(xlUp).Row

'Loop thru used cells of column "A"
For Each Cel In Sheets("20% 1995").Range("A4:A" & Lr)
If Cel = matr Then
Sheets("Relatorio").Range("A2") = "20% Concurso 1995"
Sheets("Relatorio").Range("E2") = Sheets("20% 1995").Cells(Cel.Row, 30)
Exit Sub 'End if matr is found one time
End If
Next Cel
End Sub

rodtt
07-24-2013, 11:49 AM
Here is the file you asked

rodtt
07-24-2013, 01:37 PM
The last two lines are outside the loop so they run every time the loop ends.

y = Sheets("20% 1995").Rows.Count is the same as y = bottom of sheet. So Y is always less than Rows.Count.

This sub only looks at the used cells in Column "A". IF it ever finds matr, it runs the (not anymore) last two lines then stops.


Sub SamT()
Dim matr As Long, Lr As Long '"Lr" = "LastRow"
Dim Cel As Range

matr = Sheets("Referencia").Range("B5")
Lr = Sheets("20% 1995").Cells(Rows.Count, 1).End(xlUp).Row

'Loop thru used cells of column "A"
For Each Cel In Sheets("20% 1995").Range("A4:A" & Lr)
If Cel = matr Then
Sheets("Relatorio").Range("A2") = "20% Concurso 1995"
Sheets("Relatorio").Range("E2") = Sheets("20% 1995").Cells(Cel.Row, 30)
Exit Sub 'End if matr is found one time
End If
Next Cel
End Sub


That solved my problem.
Thanks to you, SamT, and everybody that helped!

Aussiebear
07-25-2013, 02:47 AM
Please don't quote the full response every time you need to answer. if you need to refer to a particular post then simply use the post # that you are responding too.