PDA

View Full Version : Solved: Loop not running correctly



maninjapan
11-11-2011, 10:43 AM
I am trying to run the following. It is supposed to run through to the end of the list of data. However when I run it, it seems to stop on the first cell (The first cell meets the condition and I get a value pasted to L2). It doesnt seem to keep running through the data after that though. I can't seem to figure out what is wrong with it.... Any help appreciated...



Option Explicit

Sub Tokyo_Time()
Dim i As Long, LastRow As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Range("C" & i).Value >= Range("P2").Value And Range("C" & i).Value <= Range("P3").Value Then

Do
Range("L2").Value = Range("O2").Value
Loop Until Range("C" & i).Value = ""
End If
Next i

End Sub

Paul_Hossler
11-11-2011, 02:27 PM
1. I always like to qualify my ranges: Worksheet("MyData").Range ("A1")

2. If you're going to use a For i = 2 to LastRow loop, my choice/opinion/personal style would be to use .Cells(i,3).Value

3. I don't understand the Do/Loop Until. It seems like you're just putting O2 into L2 a lot of times

4. Got a small sample you can post, along with the Before and After?

Paul

Rob342
11-11-2011, 03:02 PM
Your code goes into a endless loop
try this

Sub Tokyo_Time()
Dim i As Long, LastRow As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Range("C" & i).Value >= Range("P2").Value And Range("C" & i).Value <= Range("P3").Value Then
Range("L2").Value = Range("O2").Value
End If
If Range("c" & i).Value = "" Then Exit Sub
Next i

End Sub

mikerickson
11-11-2011, 06:34 PM
It looks like what that is doing is
"If any cell in column C is between the values in P2 and P3 then set L2 to the value in O2."

Not only is the Do Loop infinite, but no looping is needed at all
With Range("L2")
.FormulaR1C1 = "=IF(SUMPRODUCT(--(R2C16<=C3)*(C3<=R3C16)),R2C15,""" & .Value & """)"
.Value = .Value
End With

maninjapan
11-12-2011, 10:42 PM
Hi Guys, thanks for the contributions. After reviewing them I realized the glaring mistake in my code......


Range("L2").Value = Range("O2").Value
Should read
Range("L" & i).Value = Range("O2").Value


I have modified the code to the following and it now works exactly as expected!!

Thank you

Option Explicit

Sub Tokyo_Time()
Dim i As Long, LastRow As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Range("C" & i).Value >= Range("P2").Value And Range("C" & i).Value <= Range("P3").Value Then
Range("L" & i).Value = Range("O2").Value
End If
Next i

End Sub

shrivallabha
11-12-2011, 11:50 PM
You can use mikerickson's non-looping approach, especially if Column L is originally blank as below:
Sub Tokyo_Time2()
With Range("L2:L" & Range("A" & Rows.Count).End(xlUp).Row)
.FormulaR1C1 = "=IF(AND(RC[-9]>=R2C16,RC[-9]<=R3C16),R2C15,"""")"
.Value = .Value
End With
End Sub

maninjapan
11-20-2011, 11:06 PM
Thanks shriv ,another way to approach it that I didnt know of.

shrivallabha
11-21-2011, 07:22 AM
Nice that it works for you. But it was mickericson who pointed it out...so I tried to understand a few things about it here:
http://www.vbaexpress.com/forum/showthread.php?t=39788