How to insert VLOOKUP into cell with variable array
Category: [Excel] Demo Available
How to insert VLOOKUP into cell with variable array
Requirement:
The user is trying to write a macro that inserts a VLOOKUP formula into a cell. The issue is that the array that the VLOOKUP references will be on a different spreadsheet each time. The user can get the file name from the open file dialog which is chosen by the user. The user hasn't written that part yet because the user is not sure whether the final part of inserting the formula is possible.
The formula will be inserted like this:
Code:
Range("B10").Formula = "=VLOOKUP(A10,'SheetSpecifiedByUser'!A:B,2,0)"
So, how would the user insert the SheetSpecifiedByUser part into the formula?
Solution:
Code:
Range("B10").Value = "=VLOOKUP(A10,'[" & Filename & "]sheet1'!A:B,2,0)"
Obtained from the OzGrid Help Forum.
Solution provided by vba_monkey
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VLOOKUP (example) |
| How to use the VLOOKUP formula |
| How to use IF and VLOOKUP formulas together |
| How to maintain the VLOOKUP font format |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.