Working with the result of a VBA Find in Excel

Question

I've trawled through a few hours of Google looking for an answer for this, so I apologise if it seems obvious to you, it really isn't to me!

I'm trying to take a cell value from 1 workbook, search for it in another. As a result of that, select some data in the search result's row, copy and paste into a cell in the search term's row in the original workbook.

Here's what I've written:

Sub AutoCableSize()
'
' AutoCableSize Macro
Dim Row As Integer
Dim CableRef As String
Dim Rng As Integer

Rng = 0
Row = 1
CableRef = ""
Windows("170615-Submains Cable Schedule.xlsx").Activate

For Each Cell In Range("F3:F303"):
On Error Resume Next
    If CableRef = "Finish" Then
    GoTo Finish:
    End If
    CableRef = Range("F" & Row).Value
    Windows("170601-B2-3-HL_BAS_SCH_61_0001.xlsx").Activate
    Columns("A:A").Select
Selection.Find(What:=CableRef, LookIn:=xlValues _
        , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Select
        Rng = ActiveCell.Row
            If Rng = 1 Then
                GoTo Continue
            End If
        Range("C" & Rng, "D" & Rng).Copy
    Windows("170615-Submains Cable Schedule.xlsx").Activate
    Range("J" & Row).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Continue:
    Row = Row + 1
Next Cell
Finish:
End Sub

What should I put in the Find variables to search for an exact result. I have used xlWhole but I am having an issue:

If the entry does not exist, it skips to the next correctly. If the entry does exist, it selects the first blank cell in the search series, and treats that as the search result?! I have no idea why!

Answer 1

@CallumDA

In looking at your answer code, I found that in recent Excel versions, it DID NOT FUNCTION correctly - UNLESS you used exactly what the Macro recorder creates: Set X = {AnyRangeVariableHere}.Find(...) method calls fail universally to return an object instance now, leaving X = Nothing (and failing to find a target, even when a valid one exists).

The only syntax which seems to work is literally: Set X = Cells.Find(...)

If you look it all up the documents, this distinction makes no sense, but I can assure you that with Excel 2016/2019, this certainly seems to be the case. Apparently Application.Cells method is some sort of special case/subclass whereby the .Find method actually still functions and returns a range object reference.