# Max value

• ### 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 ?

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.

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