For Next Issue

  • Thread starter Thread starter darkman
  • Start date Start date
D

darkman

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 --->
 
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)
 
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.
 
Ivan said:
Hi,

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

Regards,
Ivan
Click to expand...

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?
 
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
 
Back
Top