Excel Vba Vlookup Error 2029
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 more about Stack Overflow the company Business Learn more about hiring developers or
Excel Vba Vlookup Error 1004
posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join excel vba vlookup error 2042 the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes excel vba vlookup error handling a minute: Sign up Error 2029 during vlookup in vba up vote 0 down vote favorite I am fairly new to VBA and Vlookup. The macro I am trying to create will end up running against multiple workbooks and multiple sheets
Excel Vba Vlookup In Another Worksheet
with those workbooks. So I am having the users enter the workbook names for the two workbooks that they will be using at a given time. From that I am selecting the name of the sheet that I want to use then building the a variable containing the workbook/sheet/range that I want to look for my value in. As you can see from my screen shot I am looking for the value from "C10" in "0391 - WOR" in the range
Excel Vba Vlookup Object Required
of "B1:AJ300" in "0391 Week 1". In the first vlookup I use the built path and it returns a Error 2029, when I hard code the path I pull the data.even though the the workbook/sheet/range values are the same. Sub Week1() ' ' Week1 Macro ' Dim MyRange As String Dim SalesRptName As String Dim ShPath As String Dim SheetNam As String Dim WorRptName As String ' SalesRptName = Range("Macros.xlsm!A1") WorRptName = Range("Macros.xlsm!A2") Workbooks(SalesRptName).Activate CurrSheet = ActiveSheet.Name SheetNam = CurrSheet MyRange = "$B$1:$AJ$300" ShPath = "'[" & SalesRptName & "]" & SheetNam & "'!" & MyRange SheetNam = "'[0391 - Week 1.xlsx]" Workbooks(WorRptName).Activate ' Range("D10").Select Val1 = [VLookup(C10, ShPath, 34, False)] If IsError(Val1) Then Val1 = Val1 Else If Val1 < 0 Then Val1 = Val1 * -1 Range("D10").Value = Val1 ' Range("D10").Select Val2 = [VLookup(C10,'[0391 - Week 1.xlsx]Sheet3'!$B$1:$AJ$300,34,FALSE)] If IsError(Val2) Then Val2 = 0 Else If Val2 < 0 Then Val2 = Val2 * -1 Range("D10").Value = Val2 My Watches are: Val1 Error 2029 Val2 -62.50 SHPath "'[0391 - Week 1.xlsx]Sheet3'!$B$1:$AJ$300" Data output file: 0391 WOR - FY17 Acc't Code 0125.122800 #NAME? 0125.122600 - Data - lookup file: 0391 Week1 Sales Account Number Total 0115.149100 (62.50) 0125.122800 (25.50) excel-vba vlookup share|improve this question edited Aug 26 at 0:31 A. Sarid 2,6881837 asked Aug 25 at 21:29 T. Williamson 12 add a comment| active oldest votes Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebo
Forum Microsoft Office Application Help - Excel Help forum Excel Programming / VBA / Macros vlookup in VB returns 2029 error To get replies by our experts at nominal charges, follow this link to buy excel vba vlookup copy paste value points and post your thread in our Commercial Services forum! Here is the
Excel 2007 Vba Vlookup
FAQ for this forum. + Reply to Thread Results 1 to 2 of 2 vlookup in VB returns 2029 error Thread excel vba vlookup in another workbook Tools Show Printable Version Subscribe to this Thread… Rate This Thread Current Rating Excellent Good Average Bad Terrible Display Linear Mode Switch to Hybrid Mode Switch to Threaded http://stackoverflow.com/questions/39155002/error-2029-during-vlookup-in-vba Mode 10-01-2013,06:01 PM #1 C0ppert0p View Profile View Forum Posts Registered User Join Date 02-22-2013 Location Honolulu, Hawaii MS-Off Ver Excel 2007 Posts 7 vlookup in VB returns 2029 error hi all, I have a very simple vlookup statement: cells(x,1).value = [vlookup(cells(x,2).value),$W$2:$X$75,2,false)]and I'm get "#VALUE!" for all values of x When I run the statement in Immediate mode:?[vlookup(cells(x,2).value,$W$2:$X$75,2,false)]I get Error 2015 When I use an actual value for http://www.excelforum.com/showthread.php?t=958564 cells(x,2).value ( "Inactive" )?[vlookup("Inactive",$W$2:$X$75,2,false)]I get the correct result, Oahu When I create a variable "V" and assign is the value "Inactive"?[vlookup(V,$W$2:$X$75,2,false)]I get Error 2029 When I put a formula into all the cells in row2:=VLOOKUP(B24059,W2:X75,2,FALSE)I get the correct value being returned. What is going on here? vlookup isn't rocket science, but my vb is not getting off the ground.... Any help would be appreciated Register To Reply 10-01-2013,06:02 PM #2 JieJenn View Profile View Forum Posts Visit Homepage Forum Expert Join Date 12-14-2009 Location San Francisco, CA MS-Off Ver 2010 & 2013 Posts 2,785 Re: vlookup in VB returns 2029 error Vlookup is on the Excel level not VBA level. Maybe cells(x,1).value = worksheetfunction.vlookup(cells(x,2).value,range("W2:X75"),2,false) To thank someone who has helped you, click on the star icon below their name. I hate reading Guess I am no longer young after turning 30... Register To Reply + Reply to Thread « Previous Thread | Next Thread » Thread Information Users Browsing this Thread There are currently 1 users browsing this thread. (0 members and 1 guests) Tags for this Thread vlookup error code View Tag Cloud Bookmarks Bookmarks Digg del.icio.us StumbleUpon Google Posting Permissions You may not post new threads You may not
Join INTELLIGENT WORK FORUMSFOR COMPUTER PROFESSIONALS Log In Come Join Us! Are you aComputer / IT professional?Join Tek-Tips Forums! Talk With Other Members Be Notified Of ResponsesTo Your Posts Keyword Search One-Click Access To YourFavorite Forums Automated SignaturesOn Your Posts http://www.tek-tips.com/viewthread.cfm?qid=1150282 Best Of All, It's Free! Join Us! *Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting Guidelines Promoting, selling, recruiting, coursework and thesis posting is forbidden.Tek-Tips Posting Policies Jobs Jobs from Indeed What: Where: jobs by Link To This Forum! Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.Just copy and paste the BBCode HTML Markdown MediaWiki reStructuredText code below into your vba vlookup site. VBA Visual Basic for Applications (Microsoft) Forum at Tek-Tips HomeForumsProgrammersLanguagesVBA Visual Basic for Applications (Microsoft) Forum VBA Excel Error 2029 thread707-1150282 Forum Search FAQs Links MVPs VBA Excel Error 2029 VBA Excel Error 2029 johnnymagee (Programmer) (OP) 11 Nov 05 04:59 I have a spreadsheet with dates in Column A, unit names along the top and a whole lot of vlookups on the eomonth of the dates in between. For some reason excel vba vlookup when I use VBA to read this matrix into a database, it returns "Error 2029" as the value for each vlookup. Ihave a simple workaround to replace the eomonth for this but it seems odd. Anyone know why it happens? RE: VBA Excel Error 2029 GlennUK (MIS) 11 Nov 05 05:34 And the code you use to read the matrix? How about posting that please. Cheers, Glenn.Did you hear about the literalist show-jumper?He broke his nose jumping against the clock. RE: VBA Excel Error 2029 SkipVought (Programmer) 11 Nov 05 09:11 I'd bet, it I were a betting man, that you are having an issue with DATES.Why do Dates and Times seem to be so much trouble?FAQ68-5827 Skip, Be Advised! A wee deranged psychic may be runnin' around out there!SMALL MEDUIM @ LARGE RE: VBA Excel Error 2029 johnnymagee (Programmer) (OP) 11 Nov 05 09:22 well the code is very boring,. In effect:for lCol = 2 to lNumColssUnit = sht.cells(1,lCol)for lRow = 2 to lNumRowdDay = sht.ceslls(lRow,1)debug.print sht.cells(lrow,lcol)nextnextsUnit and dDay return correct values but the vlookup in sht.cells(lrow,lcol) returns "Error 2029", but its fine in the spreadsheet Red Flag This Post Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework. Cancel Red Flag SubmittedThank you for