Hello,

I saw another post dated Nov 2004 where Dave Peterson discussed using
a global variable to overcome the problem. Refer
http://groups.google.com/group/micro...03f1507 1c036

I have encountered this issue a number of times and would like to know
what causes it.
Office 2003 / Win XP SP2

My code is outlined below;

Sub Macro1()

Dim iRow as Integer

Sheets("Sheet1").Select
Range("A1").Select

'Delete all rows until we get to a "header" row in which Column A has
the text "PO #"
While ActiveCell < "PO #"
ActiveCell.EntireRow.Delete
Wend

While ActiveCell < "End_Data"
So when I get to a line that has PO #, I move down one line and test
for the first three characters = "AUS"
If Left(ActiveCell,3) = "AUS" Then
iRow = iRow + 1
Call FormatReport1(iRow)
Call FormatReport2(iRow)

Else
ActiveCell.EntireRow.Delete
End If
Wend


End Sub


Sub FormatReport1(iRec as integer)

I need to copy from the current row, column offsets 7, 8, 9, 4, 5 and
0 to another worksheet in this workbook.

Dim strOut as String
strOut = ActiveCell(0,7) & "," & ActiveCell(0,8) & "," &
ActiveCell(0,9) & "," & ActiveCell(0,4) & "," & ActiveCell(0,5) & ","
& ActiveCell
Sheets("Report1").Select
Range("A1").Offset(iRec, 0) = strOut '
<---------------- This line also causes the control to jump.
Sheets("Sheet1").Select

End Sub


After executing the Delete statement in the first While loop, the
yellow highlight jumps to a Function Definition statement in another
module of this Workbook.

If I change the delete statement to ActiveCell.Offset(1,0).Activate,
then it works as expected.


Any explanations as to why this behaviour occurs would be greatly
appreciated.

Thank you
Mark

Is it a UDF that the execution jumps to ? Changing the worksheet would
quite likely trigger a recalculation...

Tim


"MarkM" wrote in message
...
Hello,

I saw another post dated Nov 2004 where Dave Peterson discussed using
a global variable to overcome the problem. Refer
http://groups.google.com/group/micro...03f1507 1c036

I have encountered this issue a number of times and would like to know
what causes it.
Office 2003 / Win XP SP2

My code is outlined below;

Sub Macro1()

Dim iRow as Integer

Sheets("Sheet1").Select
Range("A1").Select

'Delete all rows until we get to a "header" row in which Column A has
the text "PO #"
While ActiveCell < "PO #"
ActiveCell.EntireRow.Delete
Wend

While ActiveCell < "End_Data"
So when I get to a line that has PO #, I move down one line and test
for the first three characters = "AUS"
If Left(ActiveCell,3) = "AUS" Then
iRow = iRow + 1
Call FormatReport1(iRow)
Call FormatReport2(iRow)

Else
ActiveCell.EntireRow.Delete
End If
Wend


End Sub


Sub FormatReport1(iRec as integer)

I need to copy from the current row, column offsets 7, 8, 9, 4, 5 and
0 to another worksheet in this workbook.

Dim strOut as String
strOut = ActiveCell(0,7) & "," & ActiveCell(0,8) & "," &
ActiveCell(0,9) & "," & ActiveCell(0,4) & "," & ActiveCell(0,5) & ","
& ActiveCell
Sheets("Report1").Select
Range("A1").Offset(iRec, 0) = strOut '
<---------------- This line also causes the control to jump.
Sheets("Sheet1").Select

End Sub


After executing the Delete statement in the first While loop, the
yellow highlight jumps to a Function Definition statement in another
module of this Workbook.

If I change the delete statement to ActiveCell.Offset(1,0).Activate,
then it works as expected.


Any explanations as to why this behaviour occurs would be greatly
appreciated.

Thank you
Mark