Using Formulas Within VBA

[Ottsel]

Trying to use the Max function within VBA to find the latest date within an array of data, but I don't want to result in a formula, but rather an actual number (date in this case).

Currently I put this together, but I'm trying to go with a method that doesn't require so much time for it to run and or output the result(s).

The idea is to find the latest date and put the result in R30.

Sub test()

Sheets("Sheet2").Range("R30").Value = "=MAX(Sheet2!E32:E37)"
    With Sheets("Sheet2").Range("R30")
        .Value = Sheets("Sheet2").Range("R30").Value2
        .NumberFormat = "mm/dd/yy;@"
    End With

End Sub

I attempted to research the proper structure to pull this off, but probably wasn't using the correct phrasing. Any help or tips would be greatly appreciated!

 

[offthelip]

This VBA code finds the max value in the sheet 2 E32 to E37 without using the worksheet, ( the real coders way!!)

inarr = Worksheets("Sheet2").Range("E32:E37")
maxv = -2.2251E-308        ' start at smallest negative number
For i = 1 To UBound(inarr, 1)
  If inarr(i, 1) > maxv Then maxv = inarr(i, 1)
Next i
MsgBox maxv

 

[rollis13]

Would this give you the required result ?

Sub test2()
    Sheets("Sheet2").Range("R30") = Format(WorksheetFunction.Max(Sheets("Sheet2").Range("E32:E37")), "mm/dd/yy;@")
End Sub

P.S. Hi to all.

 

[Ottsel]

This VBA code finds the max value in the sheet 2 E32 to E37 without using the worksheet, ( the real coders way!!)

inarr = Worksheets("Sheet2").Range("E32:E37")
maxv = -2.2251E-308        ' start at smallest negative number
For i = 1 To UBound(inarr, 1)
  If inarr(i, 1) > maxv Then maxv = inarr(i, 1)
Next i
MsgBox maxv

That's an approach I wasn't expecting, but I can definitely study this to learn from it.

Was attempting to seek something a little simpler, but I appreciate it!

 

[pgc01]

Hi

Another option:

Sub test()
    
    With Worksheets("Sheet2").Range("R30")
        .NumberFormat = "mm/dd/yy;@"
        .Value = WorksheetFunction.Max(Worksheets("Sheet2").Range("E32:E37"))
    End With

End Sub

 

[Ottsel]

Would this give you the required result ?

Sub test2()
    Sheets("Sheet2").Range("R30") = Format(WorksheetFunction.Max(Sheets("Sheet2").Range("E32:E37")), "mm/dd/yy;@")
End Sub

P.S. Hi to all.

Works nicely and was the idea I had in mind, but the formatting doesn't apply.

 

[Ottsel]

Hi

Another option:

Sub test()
   
    With Worksheets("Sheet2").Range("R30")
        .NumberFormat = "mm/dd/yy;@"
        .Value = WorksheetFunction.Max(Worksheets("Sheet2").Range("E32:E37"))
    End With

End Sub

Nearly perfect, but struggling to fit this onto just 1 line if possible

Seeing how to apply the formula within VBA is helpful

 

[Ottsel]

Format(WorksheetFunction.Max(Sheets("Sheet2").Range("E32:E37")), "mm/dd/yy;@")

Would this give you the required result ?

Sub test2()
    Sheets("Sheet2").Range("R30") = Format(WorksheetFunction.Max(Sheets("Sheet2").Range("E32:E37")), "mm/dd/yy;@")
End Sub

P.S. Hi to all.

It has occurred to me that I make a mistake it appears.

This works perfectly. Thank you!

PS thanks everyone for the tips and methods.

 

[rollis13]

Thanks for the positive feedback, glad we were able to help.