Application.worksheetfunction.match Error 1004
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 application.match type mismatch developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question
Application.match Error 2042
x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; vba unable to get the match property of the worksheetfunction class it only takes a minute: Sign up Run time error '1004' Unable to get the Match propertyof the WorksheetFunction class up vote 0 down vote favorite In my macro, I have the following code : i = Application.WorksheetFunction.Match(str_accrual, Range(Selection, unable to get the match function of the worksheetfunction class Selection.End(xlToRight)), 0) where 'str_accrual' is a string captured earlier to this line and the Range selected is in a single row say from "A1" to "BH1" and the result will be a number which is the position of that string in that range selected. When I run the macro, I get the error : Run time error '1004' Unable to get the Match propertyof the WorksheetFunction class But when I run the macro line by line using (F8) key, I
Vba Error 2042
don't get this error but when I run the macro continuously I get the error. Again, if the abort the macro and run it again the error doesn't appear. Can some one let me know the solution for this. It's urgent ! Thanks in advance vba excel-vba match share|improve this question edited Sep 26 '13 at 14:32 user2140173 asked Sep 26 '13 at 14:17 user1147697 14126 The find range method works as well. msdn.microsoft.com/en-us/library/office/ff839746.aspx –Mack M. Jul 27 '15 at 21:41 add a comment| 5 Answers 5 active oldest votes up vote 1 down vote I tried several times. It seems that if there is no match, the expression will prompt this error if you want to catch the error, use Application.Match instead Then you can wrap it with isError share|improve this answer edited Sep 26 '13 at 14:49 answered Sep 26 '13 at 14:41 sam092 1,173157 add a comment| up vote 1 down vote tons of posts on this error but no solution as far as I read the posts. It seems that for various worksheet functions to work, the worksheet must be active/visible. (That's at least my latest finding after my Match() was working randomly for spurious reasons.) I hoped the mystery was solved, though activating worksheets for this kind of lookup action was a pain and costs a few CPU cycles. So I played around with syntax variations and it tu
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 unable to get the index property of the worksheetfunction class this site About Us Learn more about Stack Overflow the company Business Learn run-time error '1004' unable to get the vlookup property of the worksheetfunction class more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question
Unable To Get The Property Of The Worksheetfunction Class
x Dismiss Join 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 a minute: Sign up http://stackoverflow.com/questions/19030786/run-time-error-1004-unable-to-get-the-match-propertyof-the-worksheetfunction-c Excel VBA: Can't get a match, error “Unable to get the Match property of the WorksheetFunction class” up vote 4 down vote favorite 1 For the love of all that is good, I cannot seem to get this to work. I keep getting the error mentioned above. I have this table, and I'm trying to find out whether the code matches it's own sub-code http://stackoverflow.com/questions/17751443/excel-vba-cant-get-a-match-error-unable-to-get-the-match-property-of-the-wor somewhere within the other column, however it's erroring out. Your help is greatly appreciated. Sub testing() Dim m1 As long Dim myrange As Range Set myrange = Worksheets("Sheet1").Range("B2:B23") For e = 2 To 23 m1= Application.WorksheetFunction.Match(Cells(e, 1).Value, myrange, 0) If m1 > 0 Then Cells(e, 3).Value = "Yes" Else Cells(e, 3).Value = "No" End If Next e MsgBox "Complete!" End Sub excel vba excel-vba vlookup share|improve this question asked Jul 19 '13 at 16:49 Xtreme Havoc 842311 David's answer is correct, but why not just put the formula in column C? –Doug Glancy Jul 19 '13 at 17:01 Hi Doug, I will need to use this in conjunction with other functions that I'd like to do later on, which I can only be done optimally via VBA code. –Xtreme Havoc Jul 19 '13 at 17:19 add a comment| 2 Answers 2 active oldest votes up vote 6 down vote accepted Use the Application.Match function which allows for better ability to trap errors. When using the WorksheetFunction.Match, when a match is not found, it returns an error, which is what you're experiencing. If Not IsError(Appl
Forums Excel Questions Another WorksheetFunction.Match problem (SOLVED) Results 1 to 4 of 4 Another WorksheetFunction.Match problem (SOLVED)This is a discussion on Another WorksheetFunction.Match problem (SOLVED) within the Excel Questions forums, part of http://www.mrexcel.com/forum/excel-questions/23927-another-worksheetfunction-match-problem-solved.html the Question Forums category; I have a worksheet that acts as a database. https://coastalcoder.wordpress.com/2012/09/14/run-time-error-1004-with-excel-match-function-on-cells-containing-dates-in-vba/ Column A has their first name, Column B their last name, ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Oct 9th, 2002,05:42 PM #1 phantom1975 MrExcel MVP unable to Join Date Jun 2002 Location Omaha, Nebraska Posts 3,962 I have a worksheet that acts as a database. Column A has their first name, Column B their last name, etc. I am trying to create a UserForm that will work as a search engine. There are 2 different ways data can be searched; either by last name or by phone number. Doing the search by last name works unable to get perfectly, however, doing the search by telephone number does not. This is how the code in the UserForm looks: Private Sub BtnSearch_Click() Dim CustomerSearch as Variant Dim SearchValue as Variant Select Case BtnSearch.Caption Case "Click HERE to Search by Phone Number" CustomerSearch = Application.WorksheetFunction.Match(TxtSearch.Value,Range("Phone1"),0) Case "Click HERE to Search by Last Name" CustomerSearch = Application.WorksheetFunction.Match(TxtSearch.Value, Range("Last"),0) End Select SearchValue = Sheets("Data Sheet").Cells((CustomerSearch) + 2, 3).Value MsgBox "Is " & SearchValue & " the correct person?", vbYesNo, "Search Results" End Sub TxtSearch is the TextBox on the UserForm where the data you want searched is typed. Phone1 is a range of cells that contains phone numbers in it. When I try the search using a value in Phone1, I get the following error: Run-time error '1004': Unable to get the Match property of the WorksheetFunction class I don't get this error when I do a search by last name. I thought that it might have something to do with the value in the TextBox being considered text while the data in Phone1 was considered a number. I even tried multiplying TxtSearch.Value by 1 to convert it to a number, and it still didn't work. I have also turned off all of the forma
Dates inVBA Posted on September 14, 2012 by Me I was attempting to use the Excel worksheet function Match in VBA to find a cell with a particular date. I had the following line of code (where myDate was a variable of Date type): requiredRow = Application.WorksheetFunction.Match(myDate, Worksheets("Sheet1").Range("A1:A31"),0) This line of code was throwing the following (unhelpful) error: Runtime Error '1004' Unable to get the Match property of the WorksheetFunction class The way to get the match to work is to convert your date to a Long data type. I.e. requiredRow = Application.WorksheetFunction.Match(CLng(myDate), Worksheets("Sheet1").Range("A1:A31"),0) A very frustrating issue with a very unhelpful error message. Rate this:Like this:Like Loading... Related This entry was posted in Programming and tagged error 1004, excel, programming, vba. Bookmark the permalink. ← Find Updated Objects in SQLServer Importing Selected Data From csv File into Excel using VBA &ADO → Leave a Reply Cancel reply Enter your comment here... Fill in your details below or click an icon to log in: Email (required) (Address never made public) Name (required) Website You are commenting using your WordPress.com account. (LogOut/Change) You are commenting using your Twitter account. (LogOut/Change) You are commenting using your Facebook account. (LogOut/Change) You are commenting using your Google+ account. (LogOut/Change) Cancel Connecting to %s Notify me of new comments via email. Search for: Recent Posts Remove Characters in String T-SQL with No Loop Part2 Remove Characters in String T-SQL with NoLoop Store Dynamic CSS files in ASP.net Master Page usingC# Hotpoint Mistral Fridge Freezer TemperatureProblems. Categories Computers and Internet Language News and politics Photography Programming Shopping Uncategorized Follow Blog via Email Enter your email address to follow this blog and receive notifications of new posts by email. Join 19 other followers Meta Register Log in Entries RSS Comments RSS WordPress.com Archives Archives Select Month May 2016 May 2015 November 2014 November 2013 October 2013 August 2013 July 2013 January 2013 December 2012 November 2012 October 2012 September 2012 August 2012 July 2012 January 2011 November 2010 November 2009 April 2009 February 2009 December 2008 August 2008 May 2008 April 2008 March 2008 September 2007 August 2007 July 2007 June 2007 Besi