VBA min function loop ?

[kblaine123]

I's there a way to put this excel formula in the VBA sub below ?

If(min(D3:d$4)>e2,min(d3:d$4)-e2,0)

Cell d$4 is dynamic.....it could be 50 or 1000

I was thinking i could write a For Next statement like shown below in bold.....but I can't figure out how to write it for the min statement.


Sub AnalyzeData()
Dim i As Integer
Dim LastRow As Integer
Dim avReturn As Double
Dim stDev As Double
Dim vrnc As Double


LastRow = Sheets("Data").UsedRange.Row - 2 + Sheets("Data").UsedRange.Rows.Count


Sheets("Data").Range("L2") = LastRow


For i = 3 To LastRow
If Sheets("Data").Range("B" & i) >= Sheets("Data").Range("E" & (i - 1)) And Sheets("Data").Range("D" & i) > Sheets("Data").Range("E" & (i - 1)) Then
Sheets("Data").Range("J" & i) = (Sheets("Data").Range("D" & i) - Sheets("Data").Range("E" & i - 1))
End If
Next i

 

[hippiehacker]

this might be the formula but I don't understand for what you require the loop?

Sub test()
DMin = WorksheetFunction.Min(Cells(3, "D"), Cells(4, "D"))
If DMin > Cells(2, "E") Then
 temp = DMin - Cells(2, "E")
 Else
  temp = 0
End If
MsgBox temp
End Sub

 

[kblaine123]

HI....thinks for your effort, but i could not get it to work.

I probably don't need a loop....i'm just a rookie at this.

For further clarification

This excel formula If(min(D3:d$4)>e2,min(d3:d$4)-e2,0)is in cell I3

Now, lets say that column I is 500 rows long. I need the formula in call I3 to be copied down to the end of the rows.

cell I3 formula needs to be If(min(D3:d$500)>e2,min(d3:d$500)-e2,0)cell I4 formula needs to be If(min(D4:d$500)>e3,min(d4:d$500)-e3,0)cell I5 formula needs to be If(min(D5:d$500)>e4,min(d5:d$500)-e4,0)
all the way to row I500

Really....all i need to do is put the formula in I3 and copy it down to the end of the rows (dynamic)

 

[mmiles19]

Well, you can just highlight I3 with the formula in it and click and drag the little box in the bottom right corner of the selected I3 to move in into each cell below it until you get to 500. It will change the row each time to (dynamic).

If you really want to put it in a Sub tho, you can probably use something like this:

For i = 3 To 500[INDENT]If min(Range(Cells(i, 4), Cells(500, 4))) > Range("E2").Value Then[/INDENT]
[INDENT=2]Range("I3").Value = min(Range(Cells(i, 4), Cells(500, 4))) - E2[/INDENT]
[INDENT]Else[/INDENT]
[INDENT=2]Range("I3").Value = 0[/INDENT]
[INDENT]End If[/INDENT]
Next i
[INDENT=2]






[/INDENT]

Notice that the "4" in "Cells(i, 4)" is the 4th column (D) and goes from incrementing "i" to 500.

You might be able to play with Offset(1, 0) if you know anything about that...
​

 

[kblaine123]

Thanks mmiles19....thats probably the better way to to it.

The row count is dynamic....i just need to make that part dynamic in your for next loop.

 

[Marcelo Branco]

Really....all i need to do is put the formula in I3 and copy it down to the end of the rows (dynamic)

Maybe

Sub aTest()
    Dim lastRow As Long
    
    With Sheets("Data")
        lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
        .Range("I3:I" & lastRow).Formula = _
            "=IF(MIN(D3:D$" & lastRow & ")>E2,MIN(D3:D$" & lastRow & ")-E2,0)"
    End With
End Sub

M.

 

[kblaine123]

BAMMM......works perfectly, exactly what i needed.

You guys really have great minds ! Thank you so much.

Maybe

Sub aTest()
    Dim lastRow As Long
    
    With Sheets("Data")
        lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
        .Range("I3:I" & lastRow).Formula = _
            "=IF(MIN(D3:D$" & lastRow & ")>E2,MIN(D3:D$" & lastRow & ")-E2,0)"
    End With
End Sub

M.

 

[Rick Rothstein]

I's there a way to put this excel formula in the VBA sub below ?

If(min(D3:d$4)>e2,min(d3:d$4)-e2,0)

A shorter way to write that formula is like this...

=MAX(MIN(D3:D4)-E2,0)

BAMMM......works perfectly, exactly what i needed.

Using Marcelo's code, but substituting the VB equivalent of the formula I posted above, would give this which should also work for you...

Sub aTest2()
    Dim lastRow As Long
    
    With Sheets("Data")
        lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
        .Range("I3:I" & lastRow).Formula = "=MAX(MIN(D3:D$" & lastRow & ")-E2,0)"
    End With
End Sub

 

[kblaine123]

Thanks Rick....that works perfectly as well.
This stuff is a love/ hate thing......love to learn it, but hate that it takes me so long to wrap my brain around it at times.

A shorter way to write that formula is like this...

=MAX(MIN(D3:D4)-E2,0)



Using Marcelo's code, but substituting the VB equivalent of the formula I posted above, would give this which should also work for you...

Sub aTest2()
    Dim lastRow As Long
    
    With Sheets("Data")
        lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
        .Range("I3:I" & lastRow).Formula = "=MAX(MIN(D3:D$" & lastRow & ")-E2,0)"
    End With
End Sub

 

[Marcelo Branco]

BAMMM......works perfectly, exactly what i needed.

You guys really have great minds ! Thank you so much.

You are welcome and thanks for the feedback.

Now you have two ways (two formulas) to do what you need.
(I have to say: Rick's formula is shorter and clever)

Good luck!

M.