Hi,

I am having an issue with a For Next loop where the current loop value
seems to be incrementing before getting to "Next".

Basically I want to loop through a number of periods (months) picking
up any rows that have a date that falls within the current period. The
code seems to work fine but for some reason it jumps from X1 = 1 to X1
= 2 or X1 = 3 before getting to Next - which means it is missing
picking up any data for those months.

Any ideas?

Thanks

My Code ---

For X1 = 1 To cboPeriods

If Not IsEmpty(ActiveCell.Value) Then

Date3 = DateAdd("m", 1, Date2)
Date4 = DateAdd("d", -1, Date3)

Do Until IsEmpty(ActiveCell.Value)

Date1 = ActiveCell.Value

If Date1 = Date2 And Date1 < Date4 Then
dblThisJob = ActiveCell.Offset(0, 3).Value
Call PerformCalc(Date1, X1, ActiveCell.Value, dblThisJob)
End If

ActiveCell.Offset(1, 0).Select
Loop

Else

' ## due to either there being no job/change requests or the first
job/change request
' ## is listed in cell 'A3'
MsgBox "Error: There is no Job/Change Request listed in cell 'A2'.
There must be a value in this first cell", vbCritical

End If

Date2 = DateAdd("m", 1, Date2)
Application.Goto (Worksheets("ChangeRequests").Range("A2"))

Next X1

End Code ---


--
darkman
------------------------------------------------------------------------
darkman's Profile: http://www.excelforum.com/member.php...o&userid=34689
View this thread: http://www.excelforum.com/showthread...hreadid=544554

Hi,

you need to rearange your code to get rid of "ActiveCell.Offset(1,
0).Select" in the inner loop.

Regards,
Ivan

But you probably need to put it in the outer loop to ensure that the loop
moves on.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Ivan Raiminius" wrote in message
oups.com...
Hi,

you need to rearange your code to get rid of "ActiveCell.Offset(1,
0).Select" in the inner loop.

Regards,
Ivan

You don't use X1 except in the call to PerformCalc. If it is getting changed
prematurely, it would be in PerformCalc. Possibly pass it byVal instead of
the default byRef.

--
Regards,
Tom Ogilvy


"darkman" wrote:


Hi,

I am having an issue with a For Next loop where the current loop value
seems to be incrementing before getting to "Next".

Basically I want to loop through a number of periods (months) picking
up any rows that have a date that falls within the current period. The
code seems to work fine but for some reason it jumps from X1 = 1 to X1
= 2 or X1 = 3 before getting to Next - which means it is missing
picking up any data for those months.

Any ideas?

Thanks

My Code ---

For X1 = 1 To cboPeriods

If Not IsEmpty(ActiveCell.Value) Then

Date3 = DateAdd("m", 1, Date2)
Date4 = DateAdd("d", -1, Date3)

Do Until IsEmpty(ActiveCell.Value)

Date1 = ActiveCell.Value

If Date1 = Date2 And Date1 < Date4 Then
dblThisJob = ActiveCell.Offset(0, 3).Value
Call PerformCalc(Date1, X1, ActiveCell.Value, dblThisJob)
End If

ActiveCell.Offset(1, 0).Select
Loop

Else

' ## due to either there being no job/change requests or the first
job/change request
' ## is listed in cell 'A3'
MsgBox "Error: There is no Job/Change Request listed in cell 'A2'.
There must be a value in this first cell", vbCritical

End If

Date2 = DateAdd("m", 1, Date2)
Application.Goto (Worksheets("ChangeRequests").Range("A2"))

Next X1

End Code ---


--
darkman
------------------------------------------------------------------------
darkman's Profile: http://www.excelforum.com/member.php...o&userid=34689
View this thread: http://www.excelforum.com/showthread...hreadid=544554

Ivan Raiminius Wrote:
Hi,

you need to rearange your code to get rid of "ActiveCell.Offset(1,
0).Select" in the inner loop.

Regards,
Ivan

If I remove that code my function ceases to be of any use...

Why is the offset causing the X1 to increment?

Any suggestions of a better way to do it?


--
darkman
------------------------------------------------------------------------
darkman's Profile: http://www.excelforum.com/member.php...o&userid=34689
View this thread: http://www.excelforum.com/showthread...hreadid=544554

Hi,

offset isn't causing X1 to increment.

I couldn't test your code, so I couldn't see what really happens.
Probably you are sure that X1 changes? In your code I can see that
activecell.value (in the call to PerformCalc) changes, because of
"ActiveCell.Offset(1, 0).Select" in the inner loop. X1 must be changed
somewhere else then in code you posted (because it is not used except
in the call to PerformCalc).

Regards,
Ivan