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
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