Solved: Loop In VB Code

[nickirvine]

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.

[VBA]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
[/VBA]

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

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]

perhaps

[VBA]
For i = 4 To 69
....
...
Next
[/VBA]

and
[VBA]
Range("R" & i)[/VBA]

[mancubus]

JOOC, why cell values for defining ranges?

[nickirvine]

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]

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)

[vba]
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
[/vba]

[Bob Phillips]

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)

[vba]
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
[/vba]

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

[vba]

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
[/vba]

[mancubus]

thanks for the correction xld.



before editing it was

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