How to find a value with the Find function?

Question

How do I find a value with the Find function?

I want to copy specific data from an external Excel file to the current workbook.

I added Option Explicit to test for errors but it could just spot that I didn't declare the variable. The output is the same.

Sub ReadDataFromCloseFile()
'
' ReadDataFromCloseFile Macro
'

'
 On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    Dim src As Workbook
    
    ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
    Set src = Workbooks.Open("C:\test.xlsm", True, True)
    
    
    Dim masterRow_count As Integer
    
    masterRow_count = wb.Worksheets("Sheet1").Range("A1").End(xlDown).Row       
    
    Dim row_number As Integer
       
    row_number = 2                                                              
    
    Dim strSearch As String
    Dim searchrange As Range
    
    Do
        Dim result As Range
        strSearch = wb.Worksheets("Sheet1").Range("A" & row_number).Value       
        Set searchrange = src.Worksheets("Sheet1").Range("D:D")                     
        Set result = searchrange.Find(what:=strSearch, LookIn:=xlValues, lookat:=xlValues)
        If Not result Is Nothing Then
            
            'Get the data from Asiamiles
            src.Worksheets("Sheet1").Range("AB" & result.Row).Copy wb.Worksheets("Sheet1").Range("B", row_number)
            src.Worksheets("Sheet1").Range("J" & result.Row).Copy wb.Worksheets("Sheet1").Range("C", row_number)
            src.Worksheets("Sheet1").Range("I" & result.Row).Copy wb.Worksheets("Sheet1").Range("D", row_number)
            src.Worksheets("Sheet1").Range("N" & result.Row).Copy wb.Worksheets("Sheet1").Range("E", row_number)
            src.Worksheets("Sheet1").Range("AD" & result.Row).Copy wb.Worksheets("Sheet1").Range("F", row_number)
            src.Worksheets("Sheet1").Range("P" & result.Row).Copy wb.Worksheets("Sheet1").Range("G", row_number)
            src.Worksheets("Sheet1").Range("Q" & result.Row).Copy wb.Worksheets("Sheet1").Range("H", row_number)
            
        End If
        
        row_number = row_number + 1
        
    Loop Until row_number = masterRow_count

    src.Close SaveChanges:=False            
    Set src = Nothing
    
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

There is another problem .It could not close the Excel workbook. But that is not the largest issue.

should be Set searchrange = src.Worksheets("Sheet1").Range("D:D") (with a Set) BTW using Option Explicit would help to identify these errors) — chris neilsen
– chris neilsen, Commented Nov 17, 2021 at 5:10
I added the Option Explicit and also setting the variables but still nothing has changed. I still cannot copy specific data from external excel file to the current workbook. — Zach
– Zach, Commented Nov 17, 2021 at 5:17
edit your Q to show those changes, I'll see if I can spot your error — chris neilsen
– chris neilsen, Commented Nov 17, 2021 at 5:19
And, specifically, did you add Dim searchrange As Range, result As Range, and change to Set searchrange = src.Worksheets("Sheet1").Range("D:D") and Set result = searchrange.Find(... — chris neilsen
– chris neilsen, Commented Nov 17, 2021 at 5:22
LookAt:=xlValues ?? should be LookAt:=xlPart or LookAt:=xlWhole — CDP1802
– CDP1802, Commented Nov 17, 2021 at 12:07

CDP1802 · Accepted Answer · 2021-11-17 12:33:39Z

LookAt:=xlValues should be LookAt:=xlPart or LookAt:=xlWhole, Range("B", row_number) should be Range("B" & row_number)

Option Explicit

Sub ReadDataFromCloseFile()
    
    Const SRC_WB = "C:\test.xlsm"
    
    Dim wb As Workbook, wbSrc As Workbook
    Dim ws As Worksheet, wsSrc As Worksheet
    Dim masterRow_count As Long, row_number As Long
    Dim rngSearch As Range, rngResult As Range, strSearch As String
    Dim i As Long, n As Long, ar, t0 As Single
    t0 = Timer
     
    ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
    Application.ScreenUpdating = False
    Set wbSrc = Workbooks.Open(SRC_WB, True, True)
    Set wsSrc = wbSrc.Worksheets("Sheet1")
    With wsSrc
        i = .Cells(.Rows.Count, "D").End(xlUp).Row
        Set rngSearch = wsSrc.Range("D1:D" & i)
    End With
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1")
    ar = Split("AB,J,I,N,AD,P,Q", ",")
   
    With ws
        masterRow_count = .Range("A" & .Rows.Count).End(xlUp).Row
        For row_number = 2 To masterRow_count
                
            strSearch = .Range("A" & row_number).Value
            Set rngResult = rngSearch.Find(what:=strSearch, _
                            LookIn:=xlValues, lookat:=xlWhole)
                            
            If Not rngResult Is Nothing Then
                'Get the data from Asiamiles
                For i = 0 To UBound(ar)
                   .Cells(row_number, "B").Offset(0, i) = wsSrc.Cells(rngResult.Row, ar(i))
                Next
                n = n + 1
            End If
            
        Next
    End With
    wbSrc.Close SaveChanges:=False
    Application.ScreenUpdating = True
    MsgBox row_number - 1 & " rows scanned, " & _
           n & " rows updated", vbInformation, Format(Timer - t0, "0.0 secs")
    
End Sub

Collectives™ on Stack Overflow

How to find a value with the Find function?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related