none
Max Value from column of data in spreadsheet RRS feed

  • Question

  • I am using the following code to find the maximum value in an array I call "LegalLevelArray". The array size is dynamic (i.e. # of rows increases or decreases continuously). The array represents column Q in a spreadsheet. The objective of the max value search is to find the highest value in column Q and to use this value elsewhere in the program.

    When I run the code i receive the following error message: Run Time Error 9 subscript out of range.

    Dim LegalLevelArray
    Sheets("sheet3").Range(Cells(1, "q"), Cells(LastRow, "q")).Value = LegalLevelArray
    MaxLegalLeval = Application.WorksheetFunction.Max(vLegalLevelArray)
    Sheets("Price List Matrix").Cells(3, 1) = MaxLegalLevel

    Sunday, July 10, 2011 11:43 AM

All replies

  • On Sun, 10 Jul 2011 11:43:49 +0000, Mark Bristoll wrote:
     
    >
    >
    >I am using the following code to find the maximum value in an array I call "LegalLevelArray". The array size is dynamic (i.e. # of rows increases or decreases continuously). The array represents column Q in a spreadsheet. The objective of the max value search is to find the highest value in column Q and to use this value elsewhere in the program.
    >
    >When I run the code i receive the following error message: Run Time Error 9 subscript out of range.
    >
    >Dim LegalLevelArray
    >Sheets("sheet3").Range(Cells(1, "q"), Cells(LastRow, "q")).Value = LegalLevelArray
    >MaxLegalLeval = Application.WorksheetFunction.Max(vLegalLevelArray)
    >Sheets("Price List Matrix").Cells(3, 1) = MaxLegalLevel
     
    It is difficult to tell what you are doing and doing wrong without the rest of the code.
     
    In line 1 you Dim the variable LegalLevelArray as a variant.
    You give no indication as to how LastRow is determined.
    Then, in line 2, you set the value of a dynamically sized range to the value of LegalLevelArray.  Unless you have some code between those two lines that provide values to LegalLevelArray, that variable is empty, so your dynamically sized range will be emptied (e.g. blank cells).
     
    To get the highest value in column Q, you could simply use:
     
    MaxLegalLevel = worksheetfunction.Max(range("Q:Q"))
     
    To complicate things, if you are setting LastRow in some way so as to exclude data below some given row in "Q", you could do something like:
    Note that LegalLevelArray is Dim'd as Range.
     
    If you want to Dim is as a variant, and dump the worksheet values into it, you'll need to loop through all the array elements to find the Max.
     
    ============================
    Dim LastRow As Long
        Dim MaxLegalLevel As Double
        Dim LegalLevelArray As Range
    LastRow = Cells(Cells.Rows.Count, "q").End(xlUp).Row
     
    Set LegalLevelArray = Sheets("sheet3").Range(Cells(1, "q"), Cells(LastRow, "q"))
    MaxLegalLevel = Application.WorksheetFunction.Max(LegalLevelArray)
    ===========================
     

    Ron
    Sunday, July 10, 2011 2:24 PM
  • I am using the following code to find the maximum value in an array I call "LegalLevelArray". The array size is dynamic (i.e. # of rows increases or decreases continuously). The array represents column Q in a spreadsheet. The objective of the max value search is to find the highest value in column Q and to use this value elsewhere in the program.

    When I run the code i receive the following error message: Run Time Error 9 subscript out of range.

    Dim LegalLevelArray
    Sheets("sheet3").Range(Cells(1, "q"), Cells(LastRow, "q")).Value = LegalLevelArray
    MaxLegalLeval = Application.WorksheetFunction.Max(vLegalLevelArray)
    Sheets("Price List Matrix").Cells(3, 1) = MaxLegalLevel


    Ron pointed out several issues with the 4 lines of code but he missed / ignored others.

    You use LegalLevelArray and also vLegalLevelArray.  My guess is you don't have a 'Option Explicit' declarative at the top of your code module.

    You also need to ensure that *all* cell references are for the correct sheet.  When you don't qualify a range/cells reference, it applies to the active sheet.  So, use (assuming LastRow is correctly initialized, use:

    with Sheets("sheet3")
    .Range(.Cells(1, "q"), .Cells(LastRow, "q")).Value
        end with

     

     


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Sunday, July 10, 2011 7:23 PM
  • Hello Mark,

    You have got a couple of answers here but to simplify try the following code in Example 1. I have then added 2 more examples that cuts the amount of code really required.

    Example 1:

    Sub test1()

    Dim LegalLevelArray
    Dim MaxLegalLevel

    With Sheets("Sheet3")
      LegalLevelArray = .Range(.Cells(1, "Q"), _
            .Cells(.Rows.Count, "Q").End(xlUp)).Value
     
      MaxLegalLevel = Application.WorksheetFunction.Max(LegalLevelArray)
    End With

    Sheets("Price List Matrix").Cells(3, 1) = MaxLegalLevel

    End Sub

     

    Example 2:

    Sub test2()

    Dim LegalLevelArray
    Dim MaxLegalLevel

    With Sheets("Sheet3")
      MaxLegalLevel = Application.WorksheetFunction.Max(.Range(.Cells(1, "Q"), _
            .Cells(.Rows.Count, "Q").End(xlUp)))
    End With

    Sheets("Price List Matrix").Cells(3, 1) = MaxLegalLevel

    End Sub

    Example3:

    Sub test3()

    With Sheets("Sheet3")
      Sheets("Price List Matrix").Cells(3, 1) _
            = Application.WorksheetFunction.Max(.Range(.Cells(1, "Q"), _
            .Cells(.Rows.Count, "Q").End(xlUp)))
    End With

    End Sub


    Regards, OssieMac
    Monday, July 11, 2011 12:28 AM
  • thank you, your comments are helpful and appreciated
    Thursday, July 14, 2011 10:50 AM
  • thank you, Your comments were helpful and appreciated
    Thursday, July 14, 2011 10:51 AM
  • thank you, your commens were helpful and appreciated
    Thursday, July 14, 2011 10:51 AM