CODEWITHSUNDEEP
excelvba
nad
nad

Reputation: 5

How to write maximum function in VBA?

Here's my code but it doesn't seem to work. I don't know why...

Function maximum()
Dim i As Integer
Dim dernLigne As Long
Dim somme as Variant
somme = 0
lastLigne = Range("C65536").End(xlUp).Row
Range("C65536").Value = valuemax
i = 2
While i <= lastLigne
    If Range("C" & i).Value > valeurmax Then
        valuemax = Range("C" & i).Value
        i = i + 1
    End If
Wend
maximum = valeurmax
End Function

Thanks

Upvotes: 0

Views: 2097

Answers (1)

Ralph
Ralph

Reputation: 9434

Here are two functions for your consideration:

Option Explicit

Function maxFixedRange() As Double

Dim i As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Sheet1")
For i = 2 To ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    If IsNumeric(ws.Cells(i, 3).Value2) Then
        If ws.Cells(i, 3).Value2 > maxFixedRange Then
            maxFixedRange = ws.Cells(i, 3).Value2
        End If
    End If
Next i

End Function

Function maxVariableRange(rng As Range) As Double

Dim cell As Range

For Each cell In rng
    If IsNumeric(cell.Value2) Then
        If cell.Value2 > maxVariableRange Then
            maxVariableRange = cell.Value2
        End If
    End If
Next cell

End Function

The first function looks for the maximum in a fixed range. That means that you cannot look for the maximum in a different range with that function.

The second function is expecting a range of cells. All of the cells in that range will be taken into consideration when looking for the maximum value.

enter image description here

Upvotes: 2

Related Questions