PDA

View Full Version : Return Lowest Value of Row = todays date.



wrightyrx7
03-20-2012, 01:18 PM
Hi all,

I have a spreadsheet and in column "A" i have dates that increase by 1 day going down.

For each column on the row i have prices.

I want a macro that will find todays date, return the lowest value on the row and the column header for the Column.

E.g the macro would run and for todays date (20/03/2012) would return a message box: 20/03/2012, Product 2, £5
http://s16.postimage.org/kvzu7b50l/Untitled.jpg

Thanks in advance
Chris

omp001
03-20-2012, 03:52 PM
Hi Chris.
You could try this:

Sub Test()
Dim k As Long, m As Double, h As String, x As Long
k = Range("A:A").Find(What:=Date, _
SearchDirection:=xlPrevious).Row
m = Application.Min(Range("B" & k & ":D" & k))
x = Application.Match(m, Range("B" & k & ":D" & k), 0) + 1
h = Cells(1, x).Value
MsgBox Date & ", " & h & ", " & m
End Sub

wrightyrx7
03-21-2012, 01:28 AM
Hi omp001,

Works great thank you.

How would i format the price as currency?

Regards
Chris

omp001
03-21-2012, 05:21 AM
Hi Chris.
Try this way:
MsgBox Date & ", " & h & ", " & "£ " & m