0

I have an excel file which looks up for values from another file. Now I want to change the name of the file which is being used to lookup for values through user input. What I have done is -

Dim fName as String
fName = InputBox("Enter filename")
Range("H2") = "=VLOOKUP(RC[-7],'[fname]Attendance'!R4C7:R283C9,3,0)"
...'similar vlookup lines of code

When I run this I get the inputbox to enter the filename and then the window to browse the filename for every vlookup column keeps on coming. What am I doing wrong?

Edit: I tried doing this but it gives me an error - Application defined or object-defined error.

fName = Application.GetOpenFilename(, , "Browse for Workbook")
Range("H2") = "=VLOOKUP(RC[-7],'[" & fName & "]Attendance'!R4C7:R283C9,3,0)"
    ...'similar vlookup lines of code
Pete
  • 309
  • 1
  • 4
  • 11
  • `Range("H2").FormulaR1C1 = "=VLOOKUP(RC[-7],'[" & fname & "]Attendance'!R4C7:R283C9,3,0)"` – Tim Williams Jul 13 '21 at 17:11
  • This gave me an error- `Application-defined or Object-defined error`. @Tim Williams – Pete Jul 13 '21 at 17:35
  • Is the source file open? Does the user enter the full filename including path and file extension? – Tim Williams Jul 13 '21 at 17:58
  • The source file is open and I am entering the full filename including path and file extension. – Pete Jul 13 '21 at 18:02
  • If the workbook is open then you only need the file name and extension. Try recording a macro while manually-creating a formula - does the code look different? – Tim Williams Jul 13 '21 at 18:26
  • Yes it works with only the filename and extension. However, the filename has an apostrophe so I need to write the filename as Myfile''Jan.xlsx – Pete Jul 13 '21 at 19:26
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/234833/discussion-between-pete-and-tim-williams). – Pete Jul 13 '21 at 19:56

0 Answers0