Excel Vba Vlookup Error 9
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn
Excel Vba Vlookup Error 1004
more about Stack Overflow the company Business Learn more about hiring developers or posting excel vba vlookup error 2042 ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community
Excel Vba Vlookup Error Handling
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Vlookup run-time error 9 up vote 0 down vote favorite excel vba vlookup in another worksheet I built a vlookup code, but cannot seem to understand what I have done wrong. I receive a run-time error '9': Subscript out of range error message. Cells(lrow + 1, 2) = Application.WorksheetFunction.vlookup( _ Corp.Sheets("Sheet3").Range("$B$1"), _ Workbooks("S:\_Shared Files MTL\Corporate Spreads\Weekly Sheets\" & _ myvalue & "_weekly sheet.xls"). _ Sheets("Pricing Sheet").Range("$B$18:$L$232"), 5, False) I am trying to pull data from another workbook into the current workbook (Dim Corp). The other excel vba vlookup object required workbook has a dynamic name and the date (myvalue) changes on a weekly basis. I am asking the user to enter the date with a message box: myvalue = InputBox("Insert date of file to upload in format yy_mm_dd", "User date", Format(Now(), "yy_mm_dd")) Any ideas as to why the vlookup formula is not working? excel-vba runtime-error vlookup share|improve this question edited Jun 29 at 0:23 Tim Williams 57k74163 asked Jun 28 at 20:39 Jeff 72 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote accepted You cannot run a vlookup on a workbook which is not open in Excel. You first need to open the workbook. Untested: Sub Tester() Const MY_PATH As String = "S:\_Shared Files MTL\Corporate Spreads\Weekly Sheets\" Dim myValue, fName As String, wb As Workbook, v, sht As Worksheet Set sht = ActiveSheet myValue = InputBox("Insert date of file to upload in format yy_mm_dd", _ "User date", Format(Now(), "yy_mm_dd")) fName = myValue & "_weekly sheet.xls" If Dir(MY_PATH & fName, vbNormal) <> "" Then Set wb = Workbooks.Open(MY_PATH & fName, ReadOnly:=True) v = Application.VLookup(Corp.Sheets("Sheet3").Range("$B$1"), _ wb.Sheets("Pricing Sheet").Range("$B$18:$L$232"), 5, False) sht.Cells(lrow + 1, 2) = IIf(IsError(v), "No match!", v) wb.Close False Else MsgBox "no matching file found!" End I
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow
Excel Vba Vlookup Copy Paste Value
the company Business Learn more about hiring developers or posting ads with us Stack Overflow excel 2007 vba vlookup Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of
Excel Vba Vlookup In Another Workbook
4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Runtime Error 9 Subscript Out of Range up vote 0 down vote favorite I have been tasked with http://stackoverflow.com/questions/38085870/vlookup-run-time-error-9 converting our group over to Office 2013. One of the macro enabled Excel sheets is kicking an error that I cannot figure out. Run-time Error 9 - Subscript out of range. I debug and find the ONLY code that is highlighted: W.Sheets("Sheet2") .Activate This code seems pretty straight forward and I can't seem to find any other solutions after researching quite a bit. Anyone have any ideas on troubleshooting? It works fine for http://stackoverflow.com/questions/29237334/runtime-error-9-subscript-out-of-range Office 2010 on multiple machines, in multiple locations - but in 2013 it doesn't matter which user runs it, or from where - it kicks this same error to everyone. Thanks for any help you can provide since I'm new to supporting macros and it's been awhile since I programmed anything - I'm sure it's something very simple that I'm missing. excel excel-vba excel-2013 share|improve this question edited Mar 24 '15 at 15:59 pnuts 33.8k63769 asked Mar 24 '15 at 15:58 Drew 11 possible duplicate of Subscript out of range run time error 9 –grovesNL Mar 24 '15 at 16:06 The error means what it says "Subscript Out of Range". There is no sheet named "Sheet2" within the Sheets collection. The name is the name which is displayed in the sheet tabs. It is not the "codename" which is displayed in the VBA IDE. –Axel Richter Mar 24 '15 at 16:32 These symptoms are shared with the ActiveX December update issue. –pnuts Mar 24 '15 at 18:06 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote "Sheet2" doesn't exist. The only other issue I see is the space between ("Sheet2") and .Activate, though that may just be a copy/paste issue. share|improve this answer answe
Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages Excel Web Pages | Produce Clean Efficient VBA Code Every Time | Build Automated Trading Models http://www.ozgrid.com/forum/showthread.php?t=145614 In Excel | Excel Web Pages | Excel Video Training Forum New Posts FAQ Calendar Forum Actions Mark Forums Read Quick Links Today's Posts What's New? Advanced Search Forum HELP FORUMS Excel General Run Time Error '9': Subscript Out Of Range Only with Some Users Excel Training / Excel Dashboards Reports If this is your first visit, be sure to check out the FAQ by vba vlookup clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above. Click here to view the relaunched Ozgrid newsletter. Results 1 to 9 of 9 Thread: Run Time excel vba vlookup Error '9': Subscript Out Of Range Only with Some Users Thread Tools Show Printable Version Search Thread Advanced Search August 25th, 2010 #1 patb View Profile View Forum Posts I agreed to these rules Join Date 12th August 2010 Location Chicago Posts 8 Run Time Error '9': Subscript Out Of Range Only with Some Users My macro switches back and for between two files (copying data) I thought I had all bases covered by correctly naming internally the worksheets and referencing and activating them at the appropriate times until this one user gets this error after she imports the second file and it tries to copy it's worksheet back to the first Excel file that's already open. She has Excel 2007. This macro has worked fine on multiple machines in both Excel 2003 and Excel 2007. Why now would it get this error on this machine? It needs to be loaded on dozens more and I don't want to do it knowing there may be an issue. Thanks. Excel Video Tutorials / Excel Dashboards Reports Reply With Quote August 25th, 2010 #2 AAE View Profile View Forum Posts Super Moderator Join Date 19th January 2007 Lo