loop question

**GBoy** · 03-11-2007, 08:05 PM

Hi all,

Ok I am trying to establish a loop whereby after every 6th count a variable is changed.

Therefore:-
if count = 1 to 6 then yval = 20
if count = 7 to 12 then yval = 160
if count = 13 to 18 then yval = 300

...carries on adding 140 to yval with each count range of 6.

The count is not fixed and continues until it reaches a blank cell.

I thought about dividing count by 6 and rounding down to get whole numbers that I could multiply yval by each time but there is no easy way to round down in vb that i can see.

Any ideas would be appreciated.

Thanks,
Gboy

**mudraker** · 03-11-2007, 08:46 PM

Add these commands after your counter is increased

If (counter Mod 6) = 1 And counter > 1 Then
yval = yval + 140
End If

It sounds like you are intending to use a Do type loop

Here is a way to loop using a For type loop where you get the end row number before you start the loop

Sub ddddd()
Dim counter As Integer
Dim yval As Integer
Dim iLastRow As Integer

yval = 20
iLastRow = Cells(Rows.Count, "a").End(xlUp).Row

For counter = 1 To iLastRow Step 1

If (counter Mod 6) = 1 And counter > 1 Then
yval = yval + 140
End If

Next counter

For an explanation of why I recomend For loops over Do loops see my last 2 messages at
http://www.excelforum.com/showthread.php?t=591910&page=2
End Sub