Error 2015 Vba Vlookup
Contents |
Forum Microsoft Office Application Help - Excel Help forum Excel Programming / VBA / Macros [SOLVED] error 2015 performing vlookup To get replies by our experts at nominal charges, follow this link to buy points and post your vba vlookup error 1004 thread in our Commercial Services forum! Here is the FAQ for this forum. +
Vba Vlookup Error Handling
Reply to Thread Results 1 to 2 of 2 error 2015 performing vlookup Thread Tools Show Printable Version Subscribe to this vba vlookup error 2042 Thread… Mark this thread as unsolved… Rate This Thread Current Rating Excellent Good Average Bad Terrible Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 10-18-2005,12:05 AM #1
Vba Vlookup Different Worksheet
SP Guest error 2015 performing vlookup The following code inserts #VALUE when I attempt to use the Application.Vlookup function. If I mouse over the Cells(dRow, dCol) I see error 2015 in the pop-up box. I am using Excel 2002. Any idea what is wrong ? Thanks in advance Sal Sub CreateReport() 'Declare variables Dim strFile As String Dim wkbCode As Workbook Dim wkbReport As Workbook Dim wksCustom As Worksheet Dim dCol vba vlookup another workbook As Double Dim dRow As Double Dim tmpString As String Dim tmpRange As String Set wkbCode = ThisWorkbook 'Let user choose workbook to open strFile = Application.GetOpenFilename _ ("Text Files (*.txt),*.txt, Excel Files (*.xls), *.xls", 2) If strFile <> "False" Then Set wkbReport = Workbooks.Open(strFile) End If 'Display message at bottom of screen Application.StatusBar = "Creating the report..." 'Delete "Category" column dCol = Application.Match("Category", Rows(1), 0) If Not IsError(dCol) Then Columns(dCol).Delete End If 'Insert "Manager" column between "Status" and "Caller" columns dCol = Application.Match("Caller", Rows(1), 0) If Not IsError(dCol) Then Columns(dCol).Insert End If Cells(1, dCol) = "Manager" 'look up area manager and insert into "Area Mgr" column Application.EnableEvents = False tmpRange = "[TicketReport.xls]tmp!$I$6:$J$38" For dRow = 2 To 100 tmpString = Cells(dRow, dCol + 1) Cells(dRow, dCol) = Application.VLookup(tmpString, tmpRange, 2, False) Next dRow Application.EnableEvents = True Register To Reply 10-18-2005,01:05 AM #2 Tom Ogilvy Guest Re: error 2015 performing vlookup vlookup requires a range reference as the second argument. You are supplying a string tmpRange = "[TicketReport.xls]tmp!$I$6:$J$38" so change the declaration from > Dim tmpRange As String to Dim tmpRange as Range then do set tmpRange = "Workbooks("TicketReport.xls") _ .Worksheets("Tmp").Range("I6:J38") TicketReport.xls will need to be open when you run the code. -- Regards, Tom Ogilvy "SP"
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the
Vba Code Vlookup
workings and policies of this site About Us Learn more about
Vba Vlookup Example
Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions vba vlookup excel Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. http://www.excelforum.com/showthread.php?t=477041 Join them; it only takes a minute: Sign up Application.Match Returns Error 2015 up vote 0 down vote favorite I run a VLookup in a macro and then try to obtain the row number of the matched item If currVal <> Empty Then cell.Value = Application.VLookup(currVal, rng, 1, False) If Not IsError(cell.Value) Then If cell.Value <> Empty Then 'here http://stackoverflow.com/questions/14624759/application-match-returns-error-2015 is where the code goes to pull in other info Dim temp As Variant temp = Application.Match(currVal, rng.Columns(1).Address, False) 'Debug.Print rng.Columns(1).Address 'Exit Sub End If End If End If Some additional information - the vlookup is comparing alphanumeric data and the range being compared to is defined in a different workbook. The vlookup works a treat - but the match always seems to return Error 2015. I cannot figure out why? Anyone been able to do this? excel vba share|improve this question edited Sep 11 '15 at 4:54 pnuts 33.7k63569 asked Jan 31 '13 at 11:34 user559142 3,7532985153 replace rng.Columns(1).Address with rng.Columns(1) and see if that works... –Peter Albert Jan 31 '13 at 11:38 Could you please provide actual ranges for VLOOKUP? In case different workbook is used - that's may be the case. –Peter L. Jan 31 '13 at 18:02 add a comment| active oldest votes Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook. Your Answer draft saved dr
Forums Excel Questions VBA: Vlookup won't work! Results 1 to 4 of 4 VBA: Vlookup won't work!This is a discussion http://www.mrexcel.com/forum/excel-questions/61005-visual-basic-applications-vlookup-wont-work.html on VBA: Vlookup won't work! within the Excel Questions forums, part of the Question Forums category; Hi All I have some spreadsheets in which I use the vlookup http://www.pcreview.co.uk/threads/application-worksheetfunctions.2431895/ function within VBA coding, but for some reason, ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread vba vlookup Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Sep 17th, 2003,03:37 AM #1 MartinS Board Regular Join Date Jun 2003 Location Surrey, UK Posts 382 VBA: Vlookup won't work! Hi All I have some spreadsheets in which I use the vlookup function within VBA coding, but for some vba vlookup error reason, this one won't work. I've gone back and checked references, but keep getting the error in this spreadsheet: Code: Function CALCDJC(DBPUP As String, MGP As Double) Dim MGPEND As Variant On Error GoTo CALCDJC_ERR MGPEND = WorksheetFunction.VLookup(DateValue(DBPUP), Range("MGPAMTS"), 2) Exit Function CALCDJC_ERR: CALCDJC = CVErr(xlErrValue) End Function I get the message: Error 2015 : Unable to get the VLookup property of the WorksheetFunction class 2015 The range MGPAMTS is 3 columns by 17 rows, columns 1 and 2 contain a series of dates and the final column an amount, i.e. 16/09/1968 05/08/1974 18.97 06/08/1974 31/05/1978 25.25 This is the first part of a long function, but like to test each stage as I go, which is why there is no value to return to the function as yet. Does anyone know why I can't get the lookup to work? Regards Martin Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Sep 17th, 2003,03:58 AM #2 u_niv New Member Join Date Aug 2003 Post
2015 when I call VLookUp, anyone have any ideas why? TIA Lookup Table Named Range "TestLookUp" Test Lookup Key Value 1 Value1 2 Value2 3 Value3 4 Value4 private mChanging as boolean Private Sub Worksheet_Change(ByVal Target As Range) If mChanging then exit sub If Target.Address <> "$B$1" then exit sub mChanging = true Dim sNamedRange as string sNamedRange = replace(ActiveWorkbook.Names("TestLookUp").RefersTo,"=","") If Not Application.IsError(Application.VLookup(Target.Value, sNamedRange, 2, False)) Then If Not Application.WorksheetFunction.IsNA(Application.VLookup(Target.Value, sNamedRange, 2)) Then Target.Worksheet.Range("$A$1").Formula = "VLOOKUP(""" & target.address & """,""TestLookUp"",2)" Else Target.Worksheet.Range("$A$1").Formula = "" Target.Worksheet.Range("$A$1").Value = "" End If End If mChanging = false End Sub AMDRIT, Mar 8, 2006 #1 Advertisements AMDRIT Guest This seems to work Application.VLookup(cint(target.value), ActiveWorkbook.ActiveSheet.range("TestLookUp"), 2,false) "AMDRIT" <> wrote in message news:... > Hello, > > I am recieving Error 2015 when I call VLookUp, anyone have any ideas why? > TIA > > Lookup Table Named Range "TestLookUp" > > Test Lookup > Key Value > 1 Value1 > 2 Value2 > 3 Value3 > 4 Value4 > > private mChanging as boolean > > Private Sub Worksheet_Change(ByVal Target As Range) > > If mChanging then exit sub > If Target.Address <> "$B$1" then exit sub > > mChanging = true > > Dim sNamedRange as string > sNamedRange = > replace(ActiveWorkbook.Names("TestLookUp").RefersTo,"=","") > If Not Application.IsError(Application.VLookup(Target.Value, > sNamedRange, 2, False)) Then > If Not > Application.WorksheetFunction.IsNA(Application.VLookup(Target.Value, > sNamedRange, 2)) Then > Target.Worksheet.Range("$A$1").Formula = "VLOOKUP(""" & > target.address & """,""TestLookUp"",2)" > Else > Target.Worksheet.Range("$A$1").Formula = "" > Target.Worksheet.Range("$A$1").Value = "" > End If > End If > > mChanging = false > > End Sub > AMDRIT, Mar 8, 2006 #2 Advertisements Bob Phillips Guest Vlookup expects the lookup table as arange, not a string, so maybe Application.VLookup(Target.Value, Range(sNamedRange), 2, False) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "AMDRIT" <> wrote in message news:... > Hello, > > I am recieving Error 2015 when I call VLookUp, anyone have any ideas why? > TIA > > Lookup Table Named Range "TestLookUp" > > Test Lookup > Key Value > 1 Value1 > 2 Value2 > 3 Value3 > 4