Excel停止工作，而使用查找

Question

我正在寻找一个代码，以找到一个行中的“1263_Estamp_En”在范围“J1”并wan以选择相同的列并将其粘贴到另一个工作簿，但运行代码时excel停止工作并重新启动，请帮助我如何搜索范围并选择值。下面是代码...

Sub Search() 

Dim A As Range 
Dim myRng As Range 
Dim R As Range 
Dim Col 

ThisWorkbook.Sheets("Result").Activate 
Set R = Range("A1:Z1") 
ThisWorkbook.Sheets("Sheet1").Activate 
Set A = Range("A1") 
myRng = R.Find(what:=Str(A), LookIn:=xlValue) 
Cells(myRng.Row, myRng.Column).Select 
Col = Selection.Column 
Col.select 
Range(selection,selection.end(xldown)).copy 
Thisworkbook.Sheets("Sheet2").Activate 
Range("A1").Pastespecial xlPasteValues 

End Sub 

Answer 1

我认为你正在寻找的东西像下面的代码（没有所有的不必要Activate，Selection和Select）：

Option Explicit 

Sub Search() 

Dim A As Range 
Dim myRng As Range 
Dim R As Range 
Dim Col 

Set R = Sheets("Result").Range("A1:Z1") 
Set A = Sheets("Sheet1").Range("A1") 

Set myRng = R.Find(what:=CStr(A.Value), LookIn:=xlValue, lookat:=xlWhole) 

If Not myRng Is Nothing Then ' <-- check if Find was successful 
    ' rest of your code goes here 
    myRng.EntireColumn.Copy <-- copy entire Column where found 
    Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues 
End If 

End Sub