Macro unexpectedly jumps to other macros and functions Options
 

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

Macro unexpectedly jumps to other macros and functions Options
 

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