Snasui Excel Forum

Code: Select all

Sub AddData()

Dim i As Long
Dim r As Range
Dim oFile As Variant
Dim oSheet As Variant
Dim oRange As Variant

Dim wbk As Workbook

On Error Resume Next
With Worksheets("Sheet1")
    Set r = .Range(.Range("A1"), Range("A65536").End(xlUp))
End With
    Application.EnableEvants = False
    oFile = "C:\Documents and Settings\Guest\Desktop\test\Data1.xls"
    oSheet = "Sheet1"
    
    If r.Cells(4, 3) <> "" Then
        Set wbk = Workbooks.Open(Filename:=oFile)
        Set oRange = wbk.Worksheets(oSheet).Range("G21:I21")
        
        Range("D4").Value = Application.WorksheetFunction.VLookup("¹¹.ÃÇÁ", oRange, 2, False)
        

    End If
    Application.EnableEvents = True
    
End Sub

Code: Select all

Sub AddData()

    Dim i As Long
    Dim r As Range
    Dim oFile As Variant
    Dim oSheet As Variant
    Dim oRange As Variant
    Dim wbk As Workbook
    
    'On Error Resume Next
    Application.EnableEvents = False
    
    With Worksheets("Sheet1")
        Set r = .Range(.Range("A1"), Range("A65536").End(xlUp))
    End With

    oFile = "D:\test\Data1.xls"
    oSheet = "Sheet1"
    
    If r.Cells(4, 3) <> "" Then
        Set wbk = Workbooks.Open(Filename:=oFile)
        Set oRange = wbk.Worksheets(oSheet).Range("G21:I21")
        Workbooks("Data.xls").Sheets("Sheet1") _
            .Range("D4").Value = Application.WorksheetFunction _
                .VLookup("ddd", oRange, 2, False)
    End If
    Application.EnableEvents = True
    
End Sub