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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.