mattreingold
05-24-2018, 07:42 AM
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
...
'//
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
...
'//