I have a VBA function within a spreadsheet which operates on another spreadsheet that is opened in an earlier stage of my macro. The macro used to work fine but just recently has started causing a error "Unable to get RoundDown property of the WorksheetFunction class" when it runs. I believe I understand what the error would be caused by a problem running RoundDown but I cannot see why it is getting triggered in my macro and the odd part is that when I go into Debug mode and step through the code in the VBE the error does not recur despite nothing obviously changing.
Does anyone have a similar experience of this sort of error occuring inconsistently and know what I could do to resolve it? I am wondering if there is some issue with the opened spreadsheet not being ready but I cannot see how. Substitute a different worksheet function e. Min currentCell and that also causes the same problem. Having the file open already seems to stop the problem - I wonder if there is some way that the workbook which is being opened rather than my main workbook with the macro in it is not enabled for macros and this is interfering.
But even if this is the cause I'm not sure how to get around it! This error occurs often when any argument passed to the worksheet function is not of the correct type or simply doesn't make sense. For example, I've had this problem when calling WorksheetFunction. Asin with an argument bigger than 1. In your case, I'd guess currentCell. Value is a non-numeric value or one not according to your region settings regarding numbers.
I was able to get my code to run by putting "Option Base 1" in the Declarations before the actual code section of the particular Module in the Editer. Excel assumes "Option Base 0" which will add an extra row and column of empty cells.
Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. 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. Use the Application. Match function which allows for better ability to trap errors.
When using the WorksheetFunction.
"Unable to get the VLookup property of the WorksheetFunction class"
Matchwhen a match is not found, it returns an error, which is what you're experiencing. Just as another option, this can also be done by putting the formula below in cell C2, and dragging it down to C Learn more. Asked 7 years, 2 months ago. Active 7 years, 2 months ago. Viewed 87k times.
Subscribe to RSS
Match Cells e, 1. Humble Val Humble Val 2 2 gold badges 7 7 silver badges 15 15 bronze badges. David's answer is correct, but why not just put the formula in column C? 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. Active Oldest Votes.
Unable to get the Min property of the WorksheetFunction clas
If Not IsError Application. Value, myrange, 0 Then 'Do stuff when the match is found Cells e, 3. CountIf myRange, Cells e,1. David Zemens David Zemens Quick question though: What if you wanted to "match" part of the value within the cell, what function would you use? It depends how complicated of a partial match. More complicated, and you might want to look in to using Regular Expressions.
Jaycal Jaycal 1, 1 1 gold badge 11 11 silver badges 21 21 bronze badges. Attempting to find a match to use in part with another set of codes. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password.
Post as a guest Name. Email Required, but never shown.Due to high volumes, response times in the community may be delayed over the next few days. Please refer to our self-help content for additional assistance.
Thank you! Microsoft Support. I've poked around this forum quite a bit concering the above error that I'm getting, but can't seem to find a solution catered to the code that I am working on. I have taken over the person's, who was previously in my position, code and without having being altered it began throwing up the before mentioned error on the bolded line below: Do While ActiveCell. Offset i, 0. Value, wBook. Sheets "Sales Initials".
Sheets "Loads on Hold". Range "A1". End xlDown. Workbooks "Loads on Hold. Sheets "Accounts on Hold". Offset i, 1. Offset i, 2. Offset i, 3. Offset i, 4. Offset i, 5. Do you wish to continue? Range "A2". Offset i, Range "A:N". Range "M:N". Any insight, or changes to this snippet of code would be greatly appreciated. Thank you, JMac. Did this solve your problem? Yes No. Sorry this didn't help. Thanks for your feedback. October 6, Due to high volumes, response times in the community may be delayed over the next few days.
This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread.
I have the same question The application.Forum Rules. Help Forgotten Your Password? Remember Me? Results 1 to 9 of 9. Unable to get Match property of the WorksheetFunction class.
Register To Reply.
Tom Ogilvy. RE: Unable to get Match property of the WorksheetFunction class I will add that you haven't specified the third argument to Match, so that could be problematic.
Without a third argument, it assumes the data is sorted ascending. How to handle the error when the value is not found? Worksheets "Data Entry". Match strValue, rRM? RE: Unable to get Match property of the WorksheetFunction class I'm still coming up with the same error after adding your recommandation.
Value if iserror strValue then msgbox "Value not found" exit sub end if If the value isn't found it won't raise a error - it returns a worksheet type error. This will at least give some indication if that is the problem or not. Re: Unable to get Match property of the WorksheetFunction class For some strange reason this works but now I'm having a problem with setting the Application.
Worksheet "Data Entry". Thanks for all your help. One last thing, do you know a way to check to see if a workbook is open like: if Workbook. Bookmarks Bookmarks Digg del. All times are GMT The time now is PM.Forums New posts Search forums. What's new New posts New Excel articles Latest activity. New posts. Excel Articles Latest reviews Search Excel articles. Log in Register. Search titles only. Search Advanced search….
Joined Jun 29, Messages 2. I've seen several other posts with this issue, however have not been able to find a solution. I attempted to use an IsError Function to see if the Match function just wasn't finding the string in the range, however when I get this error message after it reads the Iserror line.
I'm using a specific serach "balloki" just to simplify the function but will be using the n strings. It does find the range, when I do range1. Please help!Forums New posts Search forums. What's new New posts New Excel articles Latest activity. New posts. Excel Articles Latest reviews Search Excel articles.
Unable to get the Sum Property of the WorksheetFunction class. Thread starter clark Start date Jan 19, Joined Jan 2, Messages Here is the code that generates the error: Code:. Cells Rows. End xlUp.
Unable to get match/vlookup property of worksheetfunction class
Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Hi I'm trying to write vba to help me convert daily data into weekly data. I wrote this vba but said unable to get the average property of the worksheetfunction class. Is it possible someone can help me figure whats wrong? Also, you may need to define Range "runningagain" properly, do you mean Range runningagain? Learn more. Unable to get the average property of the worksheetfunction class Ask Question.
Asked 7 years, 2 months ago. Active 7 years, 2 months ago. Viewed 11k times. Range "D5:D11". Value ActiveCell. Offset 1, 0. Select Loop Next z End Sub.
Max Hui Max Hui 23 1 1 gold badge 1 1 silver badge 3 3 bronze badges. You may need to check Rng1 is not empty. If WorksheetFunction. You should put this as an answer. Active Oldest Votes. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Ben answers his first question on Stack Overflow.
The Overflow Bugs vs. Featured on Meta.