CODEWITHSUNDEEP
vbaexcel
honey
honey

Reputation: 199

.Find() is not working in excel file opened through another excel file

I am working on an excel file where i need to take data from another excel file which i am taking as input using VBA macro. Now if i copy the whole data from input file and paste it in main file then that works. But i want only selected data from the input file so i am using .Find() to get the required data but .Find() is not working in this input file though the same thing works in any excel file.

Here is the sample code i am using :

-----This code works------

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim responseworkbook As Workbook
    Dim macroworkbook As Workbook

    Set macroworkbook = ActiveWorkbook

    strFileToOpen = Application.GetOpenFilename _
    (Title:="Please choose a file to capture input data")

    If strFileToOpen = "" Then
        MsgBox "Please select a valid input file!"
        Exit Sub
    Else
        Workbooks.Open Filename:=strFileToOpen
        Set responseworkbook = ActiveWorkbook
        strOpenFile = ActiveWorkbook.Name
    With ActiveSheet
                lastColumn_res = .Cells(1,         Columns.Count).End(xlToLeft).Column
                lastrow_res = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
        For j = 1 To 1
            responseworkbook.Activate
            If Cells(1, j).Value = "ABC" Then 'ABC is column name
                Range(Cells(2, j), Cells(lastrow_res, j + 1)).Copy
                macroworkbook.Activate
                Sheets("selected data").Select
                With ActiveSheet
                    firstrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                End With
                Range("D" & firstrow).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
       End If
    End If

-----------------This code does not work-------------------- In above code if i replace

    Range(Cells(2, j), Cells(lastrow_res, j + 1)).Copy

with 

    rng = responseworkbook.Worksheets(1).Range("A:C").Find("12/31/2001")
    or
    rng = Sheets(1).Range("A:C").Find("12/31/2001", , xlValues, xlWhole)
    or
    rng = .Find("12/31/2001", , xlValues, xlWhole)

Here rng is a Range variable and it is not working because rng is coming as nothing.

Could you please check where i am doing it wrong because if i use the same line of code to find the data it works.

Many Thanks

Upvotes: 0

Views: 83

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

Preferably you should create a reference to the workbook you want to search, and then to the worksheet you want to search. Here's a minimal example:

Option Explicit

Sub FindInOtherWorkbook()

    Dim wbTarget As Workbook
    Dim wsTarget As Worksheet
    Dim rngTarget As Range

    Set wbTarget = Workbooks.Open("C:\Users\Robin\Desktop\foo.xlsx")
    Set wsTarget = wbTarget.Worksheets("Sheet1")
    'you can change Range("A1:F20") to whatever area you need to search in
    Set rngTarget = wsTarget.Range("A1:F20").Find(What:="foo")
    'use line below to search all cells of wsTarget worksheet
    'Set rngTarget = wsTarget.Cells.Find(What:="foo")

    If rngTarget Is Nothing Then
        Debug.Print "Cannot find foo"
    Else
        Debug.Print "Found cell at: " & rngTarget.Address
        Debug.Print "Found cell in sheet: " & rngTarget.Worksheet.Name
        Debug.Print "Found cell in book: " & rngTarget.Parent.Parent.Name
    End If

    wbTarget.Close SaveChanges:=False

End Sub

Upvotes: 1

Related Questions