0

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
Toddler
  • 95
  • 2
  • 9

1 Answers1

2

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
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
  • Runtime Error 9 subscript out of range on `Set myRng = R.Find(what:=CStr(A.Value), LookIn:=xlValue, lookat:=xlWhole)` – Toddler Apr 20 '17 at 18:17
  • @VBAToddler what about this line? I was trying to guess what are you trying to look for with the Find ? – Shai Rado Apr 20 '17 at 18:19
  • I removed `LookIn:xlValue` code is working Now the code is `Set myRng = R.Find(what:=CStr(A.Value), lookat:=xlWhole)`. Thanks – Toddler Apr 20 '17 at 18:26