Macro - find (control+f) value copied earlier in process

[michaeljamesellis]

I have the following macro where I have copied a company name under the line "Selection.Copy".

When I record the macro I paste this value in the find search box (control+f), however when I explore the captured script it is pasting the copied "Company Name" rather than selection paste (or similar).

This means as I later loop the macro it is not utilising the "Selection.Copy".

Any help really appreciated.

Sub Macro7()
'
' Macro7 Macro
'

'
Range("A2").Select
Selection.Copy
Sheets("Raw Data").Select
Columns("D:D").Select
Selection.Find(What:="Company Name", After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range("A1538:C1538").Select
Range("C1538").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Ultimate Output").Select
Range("B2").Select
ActiveSheet.Paste
Range("A2:D2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Ultimate Output (2)").Select
Range("A2").Select
ActiveSheet.Paste
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("Ultimate Output").Select
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub

 

[myall_blues]

If I've understood correctly then you need to put the copied selection into a variable, and then provide that variable as the What: in the Find.

    Dim SearchVal As Variant
    SearchVal = Range("A2").Value
    Sheets("Raw Data").Select
    Columns("D:D").Select
    Selection.Find(What:=SearchVal, After:=ActiveCell, LookIn:= _
    xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate

As an aside, notwithstanding that the macro recorder records things that way, it is almost never necessary to use .Select.
For example

Range("A2").Select
Selection.Copy

can just be written as:

Range("A2").Copy

 

[michaeljamesellis]

Thats really helpful, thank you.

Now I've implemented I can see a separate issue in that I'm selecting an absolute value rather than the 3 columns to the left of the search return.

Range("A1538:C1538").Select

Say the search puts my courser on cell D1420, I would like to be selecting / copying A1420:C1420.

This is important as the script will be looped.

 

[myall_blues]

You can get just the row number of the search result, by adding .row onto the end of the .find term. Then once you have the row number you can copy the range in A:C.

    Dim SearchVal As Variant
    Dim SearchResult As Long
    SearchVal = Worksheets("Sheet1").Range("A2").Value
    SearchResult = ThisWorkbook.Worksheets("Raw Data").Range("D:D").Find(What:=SearchVal, After:=ActiveCell, LookIn:= _
    xlValues).Row
    ThisWorkbook.Worksheets("Raw Data").Range("A" & SearchResult & ":C" & SearchResult).Copy

 

[michaeljamesellis]

I follow the idea of whats suggested and it seems correct, but I'm getting an error now when I run on this line.

Any ideas where this could be going wrong?

 

[myall_blues]

Not without knowing the error - what error do you get?
My first guess would be it didn’t find the search term.

 

[michaeljamesellis]

Run-time error '13':

Type mismatch

 

[michaeljamesellis]

When I check manually the search does return a value on line 2177

 

[myall_blues]

Change

Dim SearchVal as Variant

to

Dim SearchVal as String

 

[Alex Blakenburg]

1) Since all the original code relies on the ActiveWorkbook, I would get rid of the use of "ThisWorkbook." in the code
2) The find is erroring out because the Active Cell is on a different worksheet then the one being referenced, try just removing "after:=ActiveCell," (including the comma) from the Find line.

    SearchResult = Worksheets("Raw Data").Range("D:D").Find(What:=SearchVal,  LookIn:=xlValues).Row

Safer would be:
(since matchcase and lookat sticks if you have changed the settings)

SearchResult = Worksheets("Raw Data").Range("D:D").Find(What:=SearchVal,  LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False).Row