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!

CallumDA · Accepted Answer · 2017-06-15T12:16:20.230

Try this instead:

Option Explicit

Sub AutoCableSize()
    Dim r As Range, findRng As Range
    Dim ws1 As Worksheet, ws2 As Worksheet

    Set ws1 = Workbooks("170615-Submains Cable Schedule").Worksheets("Sheet1")
    Set ws2 = Workbooks("170601-B2-3-HL_BAS_SCH_61_0001").Worksheets("Sheet1")

    For Each r In ws1.Range("F3:F303")
        Set findRng = ws2.Columns("A:A").Find(What:=r.Value, LookIn:=xlFormulas _
            , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

        If Not findRng Is Nothing Then
            findRng.Copy
            ws1.Range("J" & r.Row).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        End If
    Next r
End Sub

I've changed your code a lot. The main thing to notice is that I haven't used Activate or Select anywhere. Referring directly to the workbook/worksheet/cell rather than activating it and working with selection is a much better style and it's the first thing to learn if you want to write error-free code.

More here: How to avoid using Select in Excel VBA macros

I am getting "Object doesn't support this property or method". I have solved this little issue by using my edited code above. But it still isn't quite working properly. It is now not finding the accurate search result, which it was before! — Philip Day, Jun 15 '17 at 12:09
Ah yes - you need to include the worksheet names too! I'll update the answer — CallumDA, Jun 15 '17 at 12:11
@PhilipDay Updated - you will need to edit the sheet names at the top though — CallumDA, Jun 15 '17 at 12:16

score 1 · Answer 2 · answered May 11 '22 at 21:18

@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.

Working with the result of a VBA Find in Excel

2 Answers2