Hi. I have many columns of numerical data with headings starting in Column B, Row 8 (all the way to Column GI in this instance but the number of columns is not always the same).
The range of values starts in Row 9 of each column (ie. just below the headings). Let's say Column C's data stops at Row 59, Column D's data stops at Row 74, and so on... each column length is different but never more than 5000 (hence the '5000' in the code below).
I would like to find the maximum value in each column and have this value placed in Row 2 of each column.
Here's my code that doesn't work...
(Note: MultiLetter is a function I have defined to convert column integer identifiers into the normal letter identifiers... ie. "3" is Column C).Sub MAXVALUES() Dim LASTCOL_I As Integer LASTCOL_I = ActiveSheet.UsedRange.Columns.Count Dim CurrentCol_I As Integer CurrentCol_I = 2 Dim CurrentCol_S As String CurrentCol_S = MultiLetter(CurrentCol_I) Do Until CurrentCol_I = LASTCOL_I + 1 Cells(CurrentCol_I, 2) = WorksheetFunction.Max(CurrentCol_S & "9" & ":" & CurrentCol_S & "5000") CurrentCol_I = CurrentCol_I + 1 CurrentCol_S = MultiLetter(CurrentCol_I) Loop End Sub
I get a VisualBasic error, Run-time error '1004': Unable to get the Max property of the WorksheetFunction class.
Clicking the 'Debug' button takes me to the Cells(CurrentCol_I, 2) =... line.
Any ideas as to how this line should be written? Or if there are other mistakes in the code?
Thanks in advance.
Bookmarks