Hi MrExcel, I want to hlookup a range of values from one workbook to another. I the looked up values to be actual numbers instead of formulas. I can get it to work without a loop, but as soon as I add the loop, it won't work. Can anyone see what is wrong from the code below? myFileName = ActiveWorkbook.Name mySheetName = ActiveSheet.Name myRangeName = Range("H156:DD166") LR = Sheets("Overview").Range("A3").End(xlDown).Row For i = 1 To LR On Error Resume Next Cells(i, 3).Formula = "worksheetfunction.hlookup(cells(i,1),[" & myFileName & "]" & mySheetName & "!" & myRangeName & ",11,false)" Next Thanks in advance

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Hlookup in a for loop to another workbook

Thread starter Ikaross
Start date Dec 8, 2015
Tags

another workbook for loop hlookup

Ikaross

New Member

Dec 8, 2015

Hi MrExcel,

I want to hlookup a range of values from one workbook to another. I the looked up values to be actual numbers instead of formulas. I can get it to work without a loop, but as soon as I add the loop, it won't work. Can anyone see what is wrong from the code below?

Rich (BB code):

myFileName = ActiveWorkbook.Name
mySheetName = ActiveSheet.Name
myRangeName = Range("H156:DD166")


LR = Sheets("Overview").Range("A3").End(xlDown).Row


For i = 1 To LR
    On Error Resume Next
        Cells(i, 3).Formula = "worksheetfunction.hlookup(cells(i,1),[" & myFileName & "]" & mySheetName & "!" & myRangeName & ",11,false)"
Next

Thanks in advance