Find row index where max value is located

[mattreingold]

I am trying to find the row index at which the maximum value of a column is located. I am trying to skip the first row as it is a header.

The 51st column is the one I am interested in ALTHOUGH, loadArr is an array of the 51st column, and if I could find the row index of the max value
of that array, that would work as well.

Currently, I am getting a mismatch error where I try to set 'dMax' to the row index's value.

Any and all help would be very much appreciated!


'//
Sub RunReport()


' Establish All Workbook and Worksheet Variables


Dim WBT As Workbook ' This Workbook


Dim WBN As Workbook ' Max Data Workbook


Dim WBD As Workbook ' Data Workbook (big excel spreadsheet)


Dim WSD As Worksheet ' Data from raw data workbook IMPORTED IN THIS WORKBOOK


Dim WSR As Worksheet ' Becomes Data in WBT


Dim WPN As Worksheet ' Report, in WBT


Dim WSM As Worksheet ' Data from max data workbook


Dim WSMR As Worksheet ' Import data from max data workbook


Dim filePathWBD As String


Dim filePathWBN As String


filePathWBD = InputBox("Enter File Path of Spreadsheet With Specimen AND Date")


Workbooks.Open (filePathWBD)


TotalRows = Rows(Rows.Count).End(xlUp).Row


Workbooks.Open (Left(filePathWBD, Len(filePathWBD) - 22))


fileNameWBD = Right(filePathWBD, 35)


fileNameWBN = Left(fileNameWBD, 13)


Set WBT = Workbooks("CompressionReport.xlsm")


Set WBD = Workbooks(fileNameWBD)


Set WBN = Workbooks(fileNameWBN)


' Establish and Copy Data Sheet from Original Workbook to RunReport Workbook


Set WSD = WBD.Worksheets("Sheet1")


WSD.Copy WBT.Sheets("Sheet1")


Set WSR = WBT.Worksheets("Sheet1 (2)")


WSR.Name = "Data"


' Establish and Copy Max Data Sheet from Original Workbook to RunReport Workbook


Set WSM = WBN.Worksheets("Sheet1")


WSM.Copy WBT.Sheets("Sheet2")


Set WSMR = WBT.Worksheets("Sheet1 (2)")


WSMR.Name = "Data2"


' Rename Sheet 1 of RunReport to Report (becomes then report tab)


Set WPN = WBT.Worksheets("Sheet1")


WPN.Name = "Report"


' Initialize Variables for Usage


Dim x As Range
Set x = Worksheets("Data").Cells
Dim y As Range
Dim z As Range
Dim a As Range
Dim b As Range
Dim c As Range
Dim d As Range
Dim e As Range
Dim f As Range
Dim g As Range
Dim h As Range
Dim i As Range


Dim loadArr()
loadArr = Range("AY2:AY" & TotalRows).Value


Dim dMax As Double
Dim lRow As Long


With WSR 'Sheet CodeName
dMax = WorksheetFunction.Max(.Columns(51))


If dMax > 0 Then
lRow = .Columns(51).Find(What:=dMax, After:=.Cells(2, 1), LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row

MsgBox "Row number with " & dMax & " is: " & lRow
End If
End With
...
'//

[p45cal]

try taking the As Double off Dim dMax As Double

[mattreingold]

I tried that and still get a mismatch on the same line, thanks for the response though! Any other suggestions?

[p45cal]

It looks like you have that data in loadArr so try .Max(loadArr) instead.
If that still causes an error, examine loadArr elements for a value which isn't Double, especially error values.

[mattreingold]

I'm gunna have to toy around with this, but I appreciate the guidance!

[jolivanes]

Just out of curiosity. What does this do?

Set x = Worksheets("Data").Cells

[mattreingold]

It sets x as all the cells in the sheet called "data" which then allows me to use .Cells.Find to negotiate through the contents of each cell (looking for headers) to discern necessary data. I then use offsets to grab the data located adjacent to the headers found through the .Find

[SamT]

Single letters from "a" to i" and "x," "Y," and "z" for variable names?!?!? Bad Dog! No biscuits for you, flayed skin, bloody beating heart...


Dim j as long
Dim ndx as long
Dim Mx

ndx = Lbound(loadArr)
Mx = loadArr(ndx)

For j = Lbound(loadArr) + 1 to UBound(loadArr)
If loadArr(j) > Mx Then
ndx = j
Mx = loadArr(ndx)
End If
Next

'Mx = Max(loadArr)
'ndx = Array Index of Max(loadArr)


Note that this will return the index of the first Maximum value encountered. If you want the last Maximum, change ">Mx" to ">=Mx"

If you want the Indices to all the (Equal) Maximums, Use ">=Mx", Declare ndx(1 to 1) As Long, then redim it inthe loop's If...THen, and store the latest j in ndx(Ubound(ndx))

[mattreingold]

SamT, I know man D: I am using them temporarily I know I know lol...

Anyways, I realized my error. In assigning loadArr, I was referencing the wrong worksheet. I am not sure what It was pointing too (the current one, whichever that is? I guess?) however I needed the data from the sheet I referenced as WSR.

Doing:


loadArr = WSR.Range("AY267:AY" & TotalRows).Value

instead of


loadArr = Range("AY267:AY" & TotalRows).Value

did the trick... duh :banghead:

Thank you all for the help!