PDA

View Full Version : Solved: Loop In VB Code



nickirvine
01-06-2011, 08:54 AM
Hi,

Wonder if you can help. I have this messy bit of VB code, its messy but it works so dont want to change it.

With Worksheets("Updates 2011").Range(Range("T15").Value & Range("R4").Value)
' if this value is greater than due dates collumn then do rest of formula, else its no
If .Value = "Y" Then
'is past the deadline
With Worksheets("Updates 2011").Range(Range("U2").Value & Range("R4").Value)
If .Value > 0 Then
'there is an update in there already
With Worksheets("Updates 2011").Range(Range("U3").Value & Range("R4").Value)
.Value = "NO"
End With
Else
' past the deadline and no update this month
With Worksheets("Updates 2011").Range(Range("U3").Value & Range("R4").Value)
.Value = "YES"
End With
End If
End With
Else
' not past the deadline
With Worksheets("Updates 2011").Range(Range("U3").Value & Range("R4").Value)
.Value = "No"
End With
End If
End With


What I would like to do is loop this code. In the above there are several bits with the reference:
Range("R4").Value

Is it possible to loop this so the next one says "Range("R5").Value", the next one says R6 and so on all the way up to R69?

I'm sure this must be doable.

Thanks for your help.

Nick

mancubus
01-06-2011, 09:13 AM
perhaps


For i = 4 To 69
....
...
Next


and

Range("R" & i)

mancubus
01-06-2011, 09:18 AM
JOOC, why cell values for defining ranges?

nickirvine
01-06-2011, 09:49 AM
Works great thanks.

I'm using the cell values for defining ranges as the cell values are the result of other formulas. Maybe not the best way in the world but it works for me!

Thanks for your help!

mancubus
01-06-2011, 10:17 AM
glad that helped.

i wonder if a shorter macro below (maybe a bit slower??) will do the job also:
(if you wish, you may test it with a backup of your file)


Dim wks As Worksheet
Set wks = Worksheets("Updates 2011")
For i = 4 To 69
' if this value is greater than due dates collumn then do rest of formula, else its no
With wks
If Range(Range("T15") & Range("R" & i)).Value = "Y" Then
'is past the deadline
If Range(Range("U2") & Range("R" & i)) > 0 Then
'there is an update in there already
Range(Range("U3") & Range("R" & i)).Value = "NO"
Else
' past the deadline and no update this month
Range(Range("U3") & Range("R" & i)).Value = "YES"
End If
Else
' not past the deadline
Range(Range("U3") & Range("R" & i)).Value = "No"
End If
End With
Next

Bob Phillips
01-06-2011, 11:33 AM
glad that helped.

i wonder if a shorter macro below (maybe a bit slower??) will do the job also:
(if you wish, you may test it with a backup of your file)


Dim wks As Worksheet
Set wks = Worksheets("Updates 2011")
For i = 4 To 69
' if this value is greater than due dates collumn then do rest of formula, else its no
With wks
If Range(Range("T15") & Range("R" & i)).Value = "Y" Then
'is past the deadline
If Range(Range("U2") & Range("R" & i)) > 0 Then
'there is an update in there already
Range(Range("U3") & Range("R" & i)).Value = "NO"
Else
' past the deadline and no update this month
Range(Range("U3") & Range("R" & i)).Value = "YES"
End If
Else
' not past the deadline
Range(Range("U3") & Range("R" & i)).Value = "No"
End If
End With
Next


If you are going to use the With construct, you must use ob ject dot qualification, otherwise it is pointless



Dim wks As Worksheet

Set wks = Worksheets("Updates 2011")
For i = 4 To 69

' if this value is greater than due dates collumn then do rest of formula, else its no
With wks

If .Range(.Range("T15") & .Range("R" & i)).Value = "Y" Then

'is past the deadline
If .Range(.Range("U2") & .Range("R" & i)) > 0 Then

'there is an update in there already
.Range(.Range("U3") & .Range("R" & i)).Value = "NO"
Else

' past the deadline and no update this month
.Range(.Range("U3") & .Range("R" & i)).Value = "YES"
End If
Else
' not past the deadline
.Range(.Range("U3") & .Range("R" & i)).Value = "No"
End If
End With
Next

mancubus
01-07-2011, 05:00 AM
thanks for the correction xld.

:banghead:

before editing it was


...
wks.Activate
For i = 4 To 69
If Range(Range("T15") & Range("R" & i)).Value = "Y" Then
...