CODEWITHSUNDEEP
vbaexcel
Nick
Nick

Reputation: 45

Find range (position) of max value in array and store in an array variable?

Looking at the bottom of this code, I need to call upon a range and the range needs to be the position of the maximum value. Is there a way to store that position of that value into a variable and then use that variable as the range?

                   Dim Retest As Boolean

                        If (Abs(Suspect - mean) / SD) > LowConf Then

                            MsgBox "95% outlier: " & Suspect
                            Retest = True

                    End If

                        If (Abs(Suspect - mean) / SD) > HighConf Then

                            MsgBox "99% outlier: " & Suspect
                            Retest = True

                    End If




                        If Retest = True And Suspect = Application.WorksheetFunction.Max(DataSet) Then

                           Range(?).Delete Shift:=xlUp

                        End If

Upvotes: 0

Views: 1579

Answers (1)

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

Finding the position of the maximum value of the range A1:A10 and highlighting the value. Try the below example and modify it per your needs,

enter image description here

Sub find()
Dim i As Long, rownum As Integer
' variable i contains the max value of range A1:A10
i = Application.WorksheetFunction.Max(Range("A1:A10"))
' rownum is the row number of the maximum value
rownum = Application.WorksheetFunction.Match(i, Range("A1:A10"), 0)
' use the rownum and highlight the cell
Range("A" & rownum).Interior.Color = vbGreen
End Sub

This vba code uses the match function to find the row number of the max value and use it in a range.

Upvotes: 1

Related Questions