VBA - Smallest number in range (greater than -1)

[Beachson]

How would I state the following in VBA? ...

If ActiveCell.Offset(0, 1) > -1 AND _
   ActiveCell.Offset(0, 2) > -1 Then

   ActiveCell.Offset(1, 0)

Else

Below is my question on how to state in VBA. Would go right after "Else" in the above code" ...

Smallest number (must be greater than -1) in the range of ActiveCell.Offset(0, 2):ActiveCell.Offset(0, 5)

Thanks for any help everyone

 

[shg]

I can't tell what you're trying to do; maybe this:

MsgBox Evaluate(Replace("=min(if(@ > -1, @))", "@", ActiveCell.Resize(5).Address))

 

[mrshl9898]

misread post, apologies

 

[Marcelo Branco]

Smallest number (must be greater than -1) in the range of ActiveCell.Offset(0, 2):ActiveCell.Offset(0, 5)

Maybe...

Dim SmallestNumber As Double

SmallestNumber = Evaluate(Replace("=MIN(IF(@>-1,@))", "@", ActiveCell.Offset(, 2).Resize(, 4).Address))
MsgBox SmallestNumber

M.

 

[Beachson]

Apologies, I wasn't clear.

Here is a repost that is clear ...

How would I state the following in VBA? ...

If ActiveCell.Offset(0, 1) > -1 AND _
   ActiveCell.Offset(0, 2) > -1 Then

   ActiveCell.Offset(1, 0)

Else

ActiveCell = [COLOR=#FF0000]Smallest number (greater than -1) within range ActiveCell.Offset(0, 2):ActiveCell.Offset(0, 5)[/COLOR]

Below is the part I need help with

ActiveCell = Smallest number (greater than -1) within range ActiveCell.Offset(0, 2):ActiveCell.Offset(0, 5)

Again, sorry for not being clear

 

[shg]

Maybe...

Thanks for the cleanup, Marcelo.

 

[Marcelo Branco]

Thanks for the cleanup, Marcelo.

The question wasn't clear.

M.

 

[Marcelo Branco]

Apologies, I wasn't clear.

ActiveCell = Smallest number (greater than -1) within range ActiveCell.Offset(0, 2):ActiveCell.Offset(0, 5)

This?

Dim SmallestNumber As Double

SmallestNumber = Evaluate(Replace("=MIN(IF(@>-1,@))", "@", ActiveCell.Offset(, 2).Resize(, 4).Address))
Application.Goto ActiveCell.Offset(, 2).Resize(, 4).Find(SmallestNumber, lookat:=xlWhole, LookIn:=xlValues)

M.

 

[Beachson]

I was not very clear on this question and obviously asked it incorrectly. Sorry everyone ...

My desired result was achieved by the following (the question I had is in red) ...

If ActiveCell.Offset(0, 1) > -1 AND _
   ActiveCell.Offset(0, 2) > -1 Then

   ActiveCell.Offset(1, 0)

Else

[COLOR=#ff0000]Selection.FormulaArray = "=MIN(IF(RC[1]:RC[4]>-1,RC[1]:RC[4]))"

[/COLOR]End If

 

[Marcelo Branco]

More robust

Dim SmallestNumber As Double, rFound As Range

SmallestNumber = Evaluate(Replace("=MIN(IF(@>-1,@))", "@", ActiveCell.Offset(, 2).Resize(, 4).Address))
Set rFound = ActiveCell.Offset(, 2).Resize(, 4).Find(SmallestNumber, lookat:=xlWhole, LookIn:=xlValues)
If Not rFound Is Nothing Then Application.Goto rFound

M.