+ Reply to Thread
Results 1 to 3 of 3

Worksheetfunction.Max without integers

  1. #1
    Registered User
    Join Date
    05-28-2013
    Location
    banbury, england
    MS-Off Ver
    Excel 2010
    Posts
    4

    Worksheetfunction.Max without integers

    Hi all,
    A small problem; I am using a Worksheetfunction.Max command to find the maximum value from more than one number on a spreadsheet, this maximum number is then stored in a string variable, for example

    sMax = Worksheetfunction.Max(x1, y1)
    x1 and y are also string variables.

    The problem occurs when one of the searched numbers cannot be found, or does not exist, on the spreadsheet. In this instance I state that the representative variable, x1 or y1, is "-". This is so that when both cannot be found, "-" is shown on the populated spreadsheet.

    When running the script with a "-" stored in one of the variables, a run-time error 1004 occurs on the worksheet function, 'Unable to get the Max property of the WorksheetFunction class.'

    Is there a way around this without having to add if functions?

    Thanks in advance.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,430

    Re: Worksheetfunction.Max without integers

    It's the same as when using MAX on the worksheet. Invalid numbers are treated as text and #N/A is returned.

    Use Variant instead of string and set value to FALSE rather than "-"
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    05-28-2013
    Location
    banbury, england
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Worksheetfunction.Max without integers

    Spot on, thanks Andy.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] A list of Consecutive Integers, can I search for missing integers
    By Harlan Grove in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 09-06-2005, 08:05 PM
  2. [SOLVED] A list of Consecutive Integers, can I search for missing integers
    By Harlan Grove in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-06-2005, 05:05 PM
  3. [SOLVED] A list of Consecutive Integers, can I search for missing integers
    By CM in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 PM
  4. [SOLVED] A list of Consecutive Integers, can I search for missing integers
    By CM in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 PM
  5. [SOLVED] A list of Consecutive Integers, can I search for missing integers
    By Harlan Grove in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 08:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1