Macro problem

[Pat]

Can anyone explain why a macro will run via Tools but when the macro is
copied and pasted into a command button it runs into trouble. When the code
gets to
"Sheets("Sheet2").Select" it hangs at "Range("AG77").Select"

Thanks if you know what the problem is.


Private Sub Sort_Click()
Range("A9:Z716").Select
Selection.Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("D9").Select
Sheets("Sheet2").Select
Range("AG77").Select
cont.......

 

[Don Guillett]

Probably something about the take focus property of the command button. I do
NOT use them. I use a forms button or a shape instead. BTW. You should
remove as many selects as possible.
Private Sub Sort_Click()
Range("A9:Z716").Sort Key1:=Range("D9"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
application.goto Sheets("Sheet2").Range("AG77")

However, depending on what you want to do with sheet2!ag57 you might not
need to go there either.

 

[Pat]

Thank you for the help!
The remaining part of the code is included here. It is now stop running at:

Selection.AutoFill Destination:=Range("AG77:AG86"), Type:=xlFillDefault

What alterations do you think I need to make here? Perhaps the remaining
code has faults also!

Private Sub Sort_Click()
Range("A9:Z716").Sort Key1:=Range("D9"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.Goto Sheets("sheet1").Range("AG77")

Selection.FormulaArray = _

"=IF(ISERROR(INDEX(sheet1!R1C9:R1999C9,MATCH(sheet2!RC[-30],sheet1!R1C4:R199
9C4,0))),"""",INDEX(sheet1!R1C9:R1999C9,MATCH(sheet2!RC[-30],sheet1!R1C4:R19
99C4,0)))"
Selection.AutoFill Destination:=Range("AG77:AG86"), Type:=xlFillDefault
Range("AG77:AG86").Select
Range("C77").Select
calculate
Sheets("sheet1").Select
Range("D10").Select
End Sub

Just out of interest what do you mean by "I use a forms button or a shape
instead. BTW."

regards
Pat

 

[Dave Peterson]

Once you stopped using Select and Selection, it broke the rest of your code.

Since this is assigned a commandbutton from the control toolbar toolbox, I'm
assuming that the code is behind one of the sheet modules.


Option Explicit
Private Sub Sort_Click()

With Me.Range("A9:Z716")
.Sort Key1:=.Columns(4), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

With Worksheets("sheet1")
.Range("AG77").FormulaArray = _
"=IF(ISERROR(INDEX(sheet1!R1C9:R1999C9," _
& "MATCH(sheet2!RC[-30],sheet1!R1C4:R1999C4,0)))," _
& """"",INDEX(sheet1!R1C9:R1999C9," _
& "MATCH(sheet2!RC[-30],sheet1!R1C4:R1999C4,0)))"

.Range("AG77").AutoFill _
Destination:=.Range("AG77:AG86"), Type:=xlFillDefault
End With

Application.Calculate
Application.Goto Worksheets("sheet1").Range("d10")

End Sub

the Me (in With Me.Range("A9:Z716")) refers to the worksheet that holds the
code. I wasn't sure if you were on sheet1, sheet2, or a different sheet.

Thank you for the help!
The remaining part of the code is included here. It is now stop running at:

Selection.AutoFill Destination:=Range("AG77:AG86"), Type:=xlFillDefault

What alterations do you think I need to make here? Perhaps the remaining
code has faults also!

Private Sub Sort_Click()
Range("A9:Z716").Sort Key1:=Range("D9"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.Goto Sheets("sheet1").Range("AG77")

Selection.FormulaArray = _

"=IF(ISERROR(INDEX(sheet1!R1C9:R1999C9,MATCH(sheet2!RC[-30],sheet1!R1C4:R199
9C4,0))),"""",INDEX(sheet1!R1C9:R1999C9,MATCH(sheet2!RC[-30],sheet1!R1C4:R19
99C4,0)))"
Selection.AutoFill Destination:=Range("AG77:AG86"), Type:=xlFillDefault
Range("AG77:AG86").Select
Range("C77").Select
calculate
Sheets("sheet1").Select
Range("D10").Select
End Sub

Just out of interest what do you mean by "I use a forms button or a shape
instead. BTW."

regards
Pat

Probably something about the take focus property of the command button. I do
NOT use them. I use a forms button or a shape instead. BTW. You should
remove as many selects as possible.
Private Sub Sort_Click()
Range("A9:Z716").Sort Key1:=Range("D9"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
application.goto Sheets("Sheet2").Range("AG77")

However, depending on what you want to do with sheet2!ag57 you might not
need to go there either.