Macro
 

What statement must be included in a macro to make it run as one, but part
one must be completed before part two starts?

Macro
 

Hard to say without knowing what's involved in your part one and part
two and what you mean by "completed".

Simplest solution is to use a single macro and put the part two stuff
after the part one stuff.

You could also run the second part as a separate macro:

Public Sub PartOne()
'Do all part one stuff
PartTwo
End Sub

Public Sub PartTwo()
'Do part two stuff stuff
End Sub




In article ,
nc wrote:

What statement must be included in a macro to make it run as one, but part
one must be completed before part two starts?

Macro
 

The first part will usually complete before the second part runs, but if this
is not occuring, try inserting a DoEvents between the two parts
--
Gary''s Student - gsnu200717


"nc" wrote:

What statement must be included in a macro to make it run as one, but part
one must be completed before part two starts?

Macro
 

Hi
Thanks for your reply. I am trying to run the following macro, only the
first sort process works when the first worksheet change takes place, the
second sort process only works for subsequent changes.


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

'First sort process

Range("Data1").Sort Key1:=Range("P18"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=M, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Second sort depedent on first sort process

rs = Application.WorksheetFunction.CountA(Range("B:B")) + 15
cs = Application.WorksheetFunction.CountA(Range("15:15" )) + 1

nb = Application.WorksheetFunction.CountBlank(Range("P1 8:P" & rs))
nf = Application.WorksheetFunction.CountIf(Range("P18:P " & rs), "FIN")
sr = rs + 1 - nb - nf
Range(Cells(sr, 1), Cells(rs, cs)).Select
Selection.Sort Key1:=Cells(sr, cs - 1), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Application.Calculate

ActiveWorkbook.RefreshAll

Application.EnableEvents = True

End Sub



"JE McGimpsey" wrote:

Hard to say without knowing what's involved in your part one and part
two and what you mean by "completed".

Simplest solution is to use a single macro and put the part two stuff
after the part one stuff.

You could also run the second part as a separate macro:

Public Sub PartOne()
'Do all part one stuff
PartTwo
End Sub

Public Sub PartTwo()
'Do part two stuff stuff
End Sub




In article ,
nc wrote:

What statement must be included in a macro to make it run as one, but part
one must be completed before part two starts?

Macro
 

Hi Gary

Thank for your suggestion. I added DoEvents between the two parts, but it
did not work. I am going to try JE McGimpsey suggestion of splitting the two
parts into two seperate macros. He wanted to have a look at the full macro I
have included it in my reply to him.

"Gary''s Student" wrote:

The first part will usually complete before the second part runs, but if this
is not occuring, try inserting a DoEvents between the two parts
--
Gary''s Student - gsnu200717


"nc" wrote:

What statement must be included in a macro to make it run as one, but part
one must be completed before part two starts?

Macro
 

Since you can only have one _Change event macro per sheet, one way
(assuming I understand you correctly):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Static bAlreadyRunOnce As Boolean

On Error GoTo ErrorHandler
Application.EnableEvents = False

If Not bAlreadyRunOnce Then
Range("Data1")...
bAlreadyRunOnce = True
Else
rs =Application.WorksheetFunction.CountA(...
...
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

In article ,
nc wrote:

Hi
Thanks for your reply. I am trying to run the following macro, only the
first sort process works when the first worksheet change takes place, the
second sort process only works for subsequent changes.


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

'First sort process

Range("Data1").Sort Key1:=Range("P18"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=M, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Second sort depedent on first sort process

rs = Application.WorksheetFunction.CountA(Range("B:B")) + 15
cs = Application.WorksheetFunction.CountA(Range("15:15" )) + 1

nb = Application.WorksheetFunction.CountBlank(Range("P1 8:P" & rs))
nf = Application.WorksheetFunction.CountIf(Range("P18:P " & rs),
"FIN")
sr = rs + 1 - nb - nf
Range(Cells(sr, 1), Cells(rs, cs)).Select
Selection.Sort Key1:=Cells(sr, cs - 1), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Application.Calculate

ActiveWorkbook.RefreshAll

Application.EnableEvents = True

End Sub



"JE McGimpsey" wrote:

Hard to say without knowing what's involved in your part one and part
two and what you mean by "completed".

Simplest solution is to use a single macro and put the part two stuff
after the part one stuff.

You could also run the second part as a separate macro:

Public Sub PartOne()
'Do all part one stuff
PartTwo
End Sub

Public Sub PartTwo()
'Do part two stuff stuff
End Sub




In article ,
nc wrote:

What statement must be included in a macro to make it run as one, but
part
one must be completed before part two starts?