Unable to get the property of the worksheetfunction class

By using our site, you acknowledge that you have read and understand our Cookie PolicyPrivacy Policyand our Terms of Service. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information.

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.

This will cause the error to occur and isn't immediately obvious to see. I have come accross this before, and for me it was becase the criteria range made no sense, as Andre said above. See example formula below:. Cells 11, i. CountIfs Sheets "Sheet1". Have a look at the Range Learn more. Asked 8 years, 4 months ago. Active 3 years, 10 months ago. Viewed 87k times. The code is here. The error occurs on the line marked with a comment. Worksheets sSheetname.By using our site, you acknowledge that you have read and understand our Cookie PolicyPrivacy Policyand our Terms of Service.

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.

unable to get the property of the worksheetfunction class

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.

unable to get the property of the worksheetfunction class

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….

Search forums. Log in. If you forgot your password, you can reset your password. JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding. Unable to get the Match Property of the WorksheetFunction class error. Thread starter wtrimble Start date Jun 29, Tags error message match function vba.

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.

Log in Register. Search titles only. Search Advanced search…. Search forums. Log in. If you forgot your password, you can reset your password. JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.

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

Some videos you may like. Excel Facts. How to create a cell-sized chart? Click here to reveal answer. Tiny charts, called Sparklines, were added to Excel Look for Sparklines on the Insert tab. Weaver Well-known Member. Joined Sep 10, Messages 5, Joined Jan 15, Messages 23, Is there an error value eg. VoG Legend. Joined Jun 19, Messages 63, You were creating a circular reference. Try Code:. Count, "I".By using our site, you acknowledge that you have read and understand our Cookie PolicyPrivacy Policyand our Terms of Service.

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.

unable to get the property of the worksheetfunction class

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.


comments

Leave a Reply

Your email address will not be published. Required fields are marked *