Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I want to use the MODE function in a vba script, but I need to apply to a range that goes across multiple worksheets. MODE doesn't work that way, but I found a solution on the web that goes like this:

    Function GetMode()
    Dim FS As Integer, LS As Integer, i As Integer, SCnt As Integer, MyArray() As Double
    FS = Sheets("First Sheet").Index
    LS = Sheets("Last Sheet").Index
    SCnt = LS - FS
    Redim MyArray(0 To SCnt)
    For i = FS To LS
    MyArray(i - FS) = Sheets(i). Range("K5")
    Next i
    GetMode = WorksheetFunction.Mode(MyArray)
    End Function

    The only problem with this is that where there are blank cells, the MODE function should ignore the contents of that cell, i.e. it only considers cells with numeric values. However with this code "MyArray" is initialized to zeros and the array value stays zero when the cell K5 is blank on a particular sheet. This is giving me an incorrect result for this function since most of the cells will be blank.

    How can I set the array value to something that will be skipped by the MODE function when the K5 cell is blank?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Declare MyArray as Variant instead of Double:

    Dim FS As Integer, LS As Integer, i As Integer, SCnt As Integer, MyArray() As Variant


  3. #3
    New Lounger
    Join Date
    Sep 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Declare MyArray as Variant instead of Double:

    Dim FS As Integer, LS As Integer, i As Integer, SCnt As Integer, MyArray() As Variant
    Excellent, thanks Hans. That seems to work exactly as required.

    I guess it's obvious when you know how. I will note it down for the future reference.

Posting Permissions

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