Vlookup till lastrow

[emukiss10]

Hello,

please help me with Vlookup VBA code to fill cells untill last row but look in sheet2 without specified name. It will differ. My table in sheet 1 has about 1 Mil rows.

Dim LR As Long


LR = Range("A" & Rows.count).End(xlUp).row

Range("K2").Formula = "=VLOOKUP(A2,[B]Sheet2!A$2:F$"[/B] & LR & ",6,FALSE)"

what next?

thx
W.

 

[JazzSP8]

Try doing...

Range("K2").AutoFill Destination:=Range("K2:K" & LR)

HTH

 

[emukiss10]

It brings up window "Update values" and wants me to select files... weird...

the whole part

Public Declare PtrSafe Function SetCurrentDirectoryA Lib "kernel32" (ByVal lpPathName As String) As Long


Sub AddOn()


'1 Choose file


Dim wb1 As Workbook
Dim wb2 As Workbook


Set wb1 = ActiveWorkbook


SetCurrentDirectoryA "\\coma.com\data\App\Ca\zz\Wk\"
FileToOpen = Application.GetOpenFilename _
(Title:="choose file", _
FileFilter:="Report Files *.txt (*.txt),")


If FileToOpen = False Then
    MsgBox "Nothing selected - end", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wb2 = Workbooks.Open(FileName:=FileToOpen)


    For Each Sheet In wb2.Sheets
        If Sheet.Visible = True Then
            Sheet.Copy After:=wb1.Sheets("ALL")
        End If
    Next Sheet


End If


    wb2.Close


'2 Text To Columns


  Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True


Sheets(1).Select

Dim LR As Long
LR = Range("A" & Rows.count).End(xlUp).row
Range("K2").Formula = "=VLOOKUP(A2,Sheet2!A$2:F$" & LR & ",6,FALSE)"
Range("K2").AutoFill Destination:=Range("K2:K" & LR)

Idk... :/

 

[emukiss10]

OK, I made a mistake but it works now. I needed to set sheet name.

thx