CODEWITHSUNDEEP
vbaexcel
Toddler
Toddler

Reputation: 95

Excel stopped working while using find

I'm Working on a code to find a row which has "1263_Estamp_En" in Range "J1" and wan to select the same column and paste it to another workbook but while running the code excel stop working and restart itself, please help how can I search the range and select the value. Below is the code...

 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

Upvotes: 0

Views: 55

Answers (1)

Shai Rado
Shai Rado

Reputation: 33662

I think you are looking for something like the code below (without all the unnecessarily Activate, Selection and 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

Upvotes: 2

Related Questions