Macro problem

  • Thread starter Thread starter Pat
  • Start date Start date
P

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.......
 
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.
 
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
 
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

Don Guillett said:
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.
Click to expand...
Click to expand...
 
Back
Top