CODEWITHSUNDEEP
vbaexcel
Vaibhav Shah
Vaibhav Shah

Reputation: 33

How do I select a range below a selected cell?

I've written a VBA that gives me the output to 1 cell- rng2.

I want to be able to check the minimum of the 3 cells below it- and see the address of the minimum cell to use it in further code.

Eg: Rng 2 is Range("AA9").

I want code to check AA10 (200), AA11 (150), AA12 (300)- compare the values and tell me which is the minimum of the three.

In this case, answer should be Range("AA11")

Upvotes: 0

Views: 2635

Answers (3)

Tehn
Tehn

Reputation: 13

You can do it using the function below (put it to vba module). Then type in excel =Maxadress(your range)

Function Maxadress(rng As Range) As String
    Maxadress = WorksheetFunction.Index(rng, WorksheetFunction.Match(WorksheetFunction.max(rng), rng, 0)).Address

End Function

Upvotes: 0

A.S.H
A.S.H

Reputation: 29332

Function minCellBelow(ByRef rng2 As Range, ByVal count As Long) As Range
    Dim rng As Range: Set rng = rng2.Offset(1).Resize(count)
    With Application.WorksheetFunction
        Set minCellBelow = rng2.Offset(.Match(.Min(rng), rng, 0))
    End With
End Function

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33682

Try the code below, explanations are inside the code as comments:

Option Explicit

Sub SelectSpecial()

Dim Rng2 As Range, Rng As Range
Dim MinVal, LRow As Variant

' modify "Sheet1" to your sheet's name
With Worksheets("Sheet1")
    Set Rng2 = .Range("AA9")

    ' set another range that starts 1 row below, and is 3 rows
    Set Rng = Rng2.Offset(1, 0).Resize(3, 1)

    ' find minimum value in Range of cells
    MinVal = WorksheetFunction.Min(Rng)

    ' find the row location of the Minimum values using the MATCH function
    LRow = Application.Match(MinVal, Rng, 0)

    ' display reult value found, and cell address
    MsgBox "Minimum Value is " & MinVal & " , located at cell " & .Range("AA" & Rng2.Row + LRow).Address
End With

End Sub

Upvotes: 1

Related Questions