Application Worksheetfunction Match Error
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
Application.match Type Mismatch
the company Business Learn more about hiring developers or posting ads with us Stack Overflow worksheetfunction.match vba Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of unable to get the match property of the worksheetfunction class number 1004 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Excel VBA: Can't get a match, error “Unable to get the Match property of the WorksheetFunction class” up
Unable To Get Match Property Of Worksheetfunction Class
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 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 =
Unable To Get Match Property Of The Worksheetfunction
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(Application.Match(Cells(e, 1).Value, myrange, 0)) Then 'Do stuff when the match is found Cells(e, 3).Value = "Yes" Else: Cells(e, 3).Value = "No" End If You could also potentially use the CountIf function: If Application.WorksheetFunction.CountIf(myRange, Cells(e,1).Value) > 0 Then Cells(e,3).Value = "Yes" Else: Cells(e,3).Value = "No" End If Neither of these approaches requires you to use the
be down. Please try the request again. Your cache administrator is webmaster. Generated Fri, 30 Sep 2016 23:15:32 GMT by s_hv987 (squid/3.5.20)
I am having trouble with error handling. Not sure how much of the code to include here, as I can’t really tell where http://www.pcreview.co.uk/threads/error-handling-with-worksheetfunction-match.3824694/ the issue is. Here’s the sub routine where it’s breaking down: http://www.vbaexpress.com/forum/archive/index.php/t-12832.html =========== MoveData: On Error GoTo BadProjectName ToRowNum = Application.WorksheetFunction.Match(ProjNme, ToRng.Columns(1).Cells, 0) On Error GoTo IrregularVendor ToColNum = Application.WorksheetFunction.Match(VendNme, ToRng.Rows(1).Cells, 0) ***err on this line*** On Error GoTo 0 ToRng.Cells(ToRowNum, ToColNum).Value = Rslt IrregularVendor: On Error GoTo 0 Return ============ The problem is that, the second time unable to that it encounters an irregular vendor, I get a run time error (Unable to get the Match property of the WorksheetFunction class). The fact that it works once leads me to believe that some sort of setting is being retained, but I can't figure out what it could be. I’ve tried inserting err.clear in various places, with unable to get no luck. If anyone has any suggestions, please let me know. Excel07, XPPro, VB6.5 -- Mike Lee McKinney,TX USA mikelee101, Jun 1, 2009 #1 Advertisements Dave Peterson Guest I would just drop the On Error stuff and .worksheetfunction and use: Dim ToRowNum as Variant 'not long, it could be an error dim ToColNum as variant 'not long torownum = application.match(projnme, torng.columns(1), 0) tocolnum = application.match(vendnme, torng.rows(1),0) if iserror(torownum) _ or iserror(tocolnum) then msgbox "at least one mismatch" else ToRng.Cells(ToRowNum, ToColNum).Value = Rslt end if mikelee101 wrote: > > Hello, > I am having trouble with error handling. Not sure how much of the code to > include here, as I can’t really tell where the issue is. Here’s the sub > routine where it’s breaking down: > =========== > MoveData: > On Error GoTo BadProjectName > ToRowNum = Application.WorksheetFunction.Match(ProjNme, > ToRng.Columns(1).Cells, 0) > > On Error GoTo IrregularVendor > ToColNum = Application.WorksheetFunction.Match(VendNme, ToRng.Rows(1).Cells, > 0) ***err on this line*** > > On Error GoTo 0 > ToRng.Cells(ToRowNum, ToColNum).Value
AMI use this coding: dim i as long for i=1 to 10 k = Application.WorksheetFunction.Match_ (Worksheets("sheet1").Range("AD" & i),_ Worksheets("sheet2").Range("B1:IV1"), 0) next but I get run time error 1004, unable to get the match property. Can u help me please?:banghead: xld05-15-2007, 03:03 AMI haven't tested it, no idea what the data looks like, but this shoule be all that yiu need Dim i As Long For i = 1 To 10 k = Application.Match(Worksheets("sheet1").Range("AD" & i), Works, 0) Next k Simon Lloyd05-15-2007, 03:20 AMNormally your worksheet function used in VBA will still take the same form of the actual worksheet function =Match(Lookup Value, Lookup Range,match type) match type is false or 0 for an exact match and True or 1 for a nearest match Simon Lloyd05-15-2007, 03:21 AMOoops, didn't want to tread on toes, i see Xld has already posted! xld05-15-2007, 03:23 AMThere is room for all! maryam05-15-2007, 04:28 AMyou mean : k= Application.Match(Worksheets("sheet1").Range("AD" & i), Worksheets("sheet2").Range("B1:IV1"), 0) it gives error 13, type mismatch Simon Lloyd05-15-2007, 05:05 AMThsi worked fine for me Dim i As Integer For i = 1 To 10 k = Application.Match(Worksheets("sheet1").Range("AD" & i), Worksheets("sheet2").Range("B1:IV1"), 0) Next i JKwan05-15-2007, 12:24 PMyou mean : k= Application.Match(Worksheets("sheet1").Range("AD" & i), Worksheets("sheet2").Range("B1:IV1"), 0) it gives error 13, type mismatch This will explain why you are getting an error http://www.cpearson.com/excel/CallingWorksheetFunctionsFromVBA.htm maryam05-15-2007, 08:19 PMno sorry now it works fine. I didnt have value in the worksheet cells. Powered by vBulletin Version 4.2.2 Copyright © 2016 vBulletin Solutions, Inc. All rights reserved.