locked
Max value RRS feed

  • Question

  • I have a column e.g values in cells from G2:G7  in ten sheets . I have to find the maximum value in the G column of ten different sheets and I have to return the name of the sheet in which the maximum value is found. I am an absolute beginner to vba .Can someone help me in this ?

    Thank you in advance

    Thursday, June 21, 2018 6:35 AM

All replies

  • something like this?

    Public Sub getMax()
    
        Dim rg As Range, ws As Worksheet, maxValue As Double
        
        maxValue = 0
        
        For Each ws In ThisWorkbook.Worksheets
            maxValue = IIf(maxValue > WorksheetFunction.Max(ws.Range("g2:g7")), maxValue, WorksheetFunction.Max(ws.Range("g2:g7")))
        Next
    
    End Sub
    

    Thursday, June 21, 2018 6:56 AM
  • It is working right but I also need to return the name of the sheet which has maximum value to my dashboard.

    Thank you for your answer.

    Thursday, June 21, 2018 2:14 PM
  • Could be done like this:

    Public Sub getMax()
    
        Dim rg As Range, ws As Worksheet, maxValue As Double, strSheetNameWithMaxValue As String
        
        maxValue = 0
        
        For Each ws In ThisWorkbook.Worksheets
            If (maxValue < WorksheetFunction.Max(ws.Range("g2:g7"))) Then
                maxValue = WorksheetFunction.Max(ws.Range("g2:g7"))
                strSheetNameWithMaxValue = ws.Name
            End If
        Next
    
    End Sub

    Friday, June 22, 2018 6:30 AM