Translating Excel formula into VBA code

[vio.coman]

Hey everybody,

I am trying to translate the following array formula into a code but it does not seem to work:

{=MIN(IF($C$5:$C$24216=$C23;IF($AL$5:$AL$24216="Apples";$AC$5:$AC$24216)))}

C$24216, $AL$24216, and AC$24216 should be of course flexible, as the lastrow varies accordingly.

Could you please give me a hint how to do it? my VBA knowledge is kind of poor..

Thanks a lot!

[snb]

Why translating ?

[vio.coman]

I meant to write the formula as a VBA code, keeping in mind that the last rows vary..

[Bob Phillips]

Dim lastrow As Long

    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
        MsgBox .Evaluate("MIN(IF($C$5:$C$" & lastrow & "=$C23,IF($AL$5:$AL$" & lastrow & "=""Apples"",$AC$5:$AC$" & lastrow & ")))")
    End With

But why? Formulas are more efficient.

[Bob Phillips]

You could also do a UDF

Public Function MinValue( _
    ByVal rngTest As Range, _
    ByVal testCell As Range, _
    ByVal rngFruit As Range, _
    ByVal rngValues As Range) As Double
Dim lastrow As Long

    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, rngTest.Column).End(xlUp).Row
        Set rngTest = rngTest.Resize(lastrow)
        Set rngFruit = rngFruit.Resize(lastrow)
        Set rngValues = rngValues.Resize(lastrow)
        MinValue = .Evaluate("MIN(IF(" & rngTest.Address & "=" & testCell.Address & ",IF(" & rngFruit.Address & "=""Apples""," & rngValues.Address & ")))")
    End With
End Function

and call from the sheet like so

=MinValue(C5,C23,AL5,AC5)

[Paul_Hossler]

@XLD - I think that if rngTest = C5, and there is only data to (say) C10 then

Set rngTest = rngTest.Resize(lastrow)

returns a range of C5:C14, not C5:C10



@vio.comam --

Another way is a sub to put the values instead of formula. I had to add another parameter to say where to put the results (A5 in the example) and for good measure since there's a lot of fruits, I decided to pass that as a second parameter

There some error protection and checks that would make this more robust

Option Explicit
Sub test()
    Call MinValues(Range("A5"), "Apples", Range("C5"), Range("C23"), Range("AL5"), Range("AC5"))
End Sub

Public Sub MinValues( _
    rngDest As Range, _
    strFruit As String, _
    rngTest As Range, _
    testCell As Range, _
    rngFruit As Range, _
    rngValues As Range)
    
    Dim lastrow As Long
     
    With ActiveSheet
         
        lastrow = .Cells(.Rows.Count, rngValues.Column).End(xlUp).Row
        Set rngTest = rngTest.Resize(lastrow - rngTest.Row + 1)
        Set rngFruit = rngFruit.Resize(lastrow - rngFruit.Row + 1)
        Set rngValues = rngValues.Resize(lastrow - rngValues.Row + 1)
        
        rngDest.FormulaArray = "=MIN(IF(" & rngTest.Address & "=" & testCell.Address & ",IF(" & rngFruit.Address & "=""" & strFruit & """," & rngValues.Address & ")))"
        rngDest.Value = rngDest.Value ' comment out for formula
        
    End With
End Sub

[vio.coman]

Thanks a lot guys!

I am now having the issue that in the line MIN(IF($C$5:$C$" & lastrow & "=$C23 does not move along with the rows.

That is, I am getting $C23 all along the way regardless I am in row 23 or some other row...

Any ideas? thanks!

[vio.coman]

Thanks Paul,

I am having the problem that the code writes the result into the first cell only, without moving down in the column...

Regards

[Paul_Hossler]

In

{=MIN(IF($C$5:$C$24216=$C23;IF($AL$5:$AL$24216="Apples";$AC$5:$AC$24216)))}

All the ranges had fixed rows and fixed columns, except $C23, so I put results into just A5

Call MinValues(Range("A5"), "Apples", Range("C5"), Range("C23"), Range("AL5"), Range("AC5"))

Going by my attachment which had data in just rows 5-10, do you want (say)


a. -- A5:A10 to have the same fixed cells EXCEPT A5 use C23, A6 use C24, A7 use C25, etc.?

b. -- A5:A10 to have the same fixed value cells and all using C23?

[Paul_Hossler]

If you're looking for this (which seems more likely to my guessing ...)

a. -- A5:A10 to have the same fixed cells EXCEPT A5 use C23, A6 use C24, A7 use C25, etc.?

which gives this


Capture.JPG


Let me know, or of course you could use the UDF approach which also has some advanages

Option Explicit

Sub test()
    Call MinValues(Range("A5"), "Apples", Range("C5"), Range("C23"), Range("AL5"), Range("AC5"))
End Sub

Public Sub MinValues( _
    rngDest As Range, _
    strFruit As String, _
    rngTest As Range, _
    testCell As Range, _
    rngFruit As Range, _
    rngValues As Range)
    
    Dim lastrow As Long
    Dim sFormula As String
     
    With ActiveSheet
         
        lastrow = .Cells(.Rows.Count, rngValues.Column).End(xlUp).Row
        Set rngDest = rngDest.Resize(lastrow - rngTest.Row + 1)
        Set rngTest = rngTest.Resize(lastrow - rngTest.Row + 1)
        Set rngFruit = rngFruit.Resize(lastrow - rngFruit.Row + 1)
        Set rngValues = rngValues.Resize(lastrow - rngValues.Row + 1)
        
        sFormula = "=MIN(IF(" & rngTest.Address(True, True) & _
            "=" & testCell.Address(False, True) & _
            ",IF(" & rngFruit.Address(True, True) & _
            "=""" & strFruit & """," & _
            rngValues.Address(True, True) & _
            ")))"
        
        'msgbox sformula
        
        rngDest.Cells(1, 1).FormulaArray = sFormula
        rngDest.Cells(1, 1).AutoFill Destination:=rngDest, Type:=xlFillDefault
        rngDest.Value = rngDest.Value
        
    End With
End Sub

VBX_Example_2.xlsm

[Bob Phillips]

@XLD - I think that if rngTest = C5, and there is only data to (say) C10 then

Set rngTest = rngTest.Resize(lastrow)

returns a range of C5:C14, not C5:C10

You are right of course, but it is worse than that as the check cell is C23. So as well as taking the first data row off the resize I think it should use the values column to calculate last row.

[vio.coman]

Thanks Paul!

I've uploaded the original file in which I am gonna be using the code.

The result of the code "as-is" in visible in column BF, whereas the result "to-be" is visible in column BK.
Something is still not working properly in the code I guess, as it does not lead to the same result as if I would do it by using the formula in column BK.
For the sake of understanding the example, the results in column BK have the formula behind, however the code should deliver only values in date format.

Do you think you can have a look?

Thanks a lot!

[snb]

A pivottable:

[Paul_Hossler]

The call was wrong, parameters out of order and AL5 instead of AL2

Call MinValues(Range("BF2"), "Maintenance", Range("C2"), Range("C2"), Range("AL5"), Range("AG2"))/

Now that I had some real data to look at, I could simplify the calling sequence, and use more meaningful variable names


Try this version, but format BF as 'Date' in number formatting

Option Explicit
Sub test2()
    Call MinValues(Range("BF2"), "Maintenance", Range("C2"), Range("AG2"), Range("T2"))
End Sub

Public Sub MinValues( _
    rGrandStartDates As Range, _
    sServiceDescription As String, _
    rContracts As Range, _
    rServiceDescriptions As Range, _
    rStartDates As Range)
     
    Dim lastrow As Long
    Dim sFormula As String
    Dim rContract As Range
     
    With ActiveSheet
         
        lastrow = .Cells(.Rows.Count, rStartDates.Column).End(xlUp).Row
        Set rGrandStartDates = rGrandStartDates.Resize(lastrow - rGrandStartDates.Row + 1)
        Set rContracts = rContracts.Resize(lastrow - rContracts.Row + 1)
        Set rContract = rContracts.Cells(1, 1)
        
        Set rServiceDescriptions = rServiceDescriptions.Resize(lastrow - rServiceDescriptions.Row + 1)
        Set rStartDates = rStartDates.Resize(lastrow - rStartDates.Row + 1)
         
        sFormula = "=MIN(IF(" & rContracts.Address(True, True) & _
        "=" & rContract.Address(False, True) & _
        ",IF(" & rServiceDescriptions.Address(True, True) & _
        "=""" & sServiceDescription & """," & _
        rStartDates.Address(True, True) & _
        ")))"
         
        rGrandStartDates.Cells(1, 1).FormulaArray = sFormula
        rGrandStartDates.Cells(1, 1).AutoFill Destination:=rGrandStartDates, Type:=xlFillDefault
        rGrandStartDates.Value = rGrandStartDates.Value
         
    End With
End Sub

[vio.coman]

Thanks a lot Paul, it works perfectly now! ;-)