CODEWITHSUNDEEP
excelvba
Ting Ping
Ting Ping

Reputation: 1145

How to find the earliest MMMYY from a date array?

Does anyone know how might I find the earliest MMMYY from an array of the following form (see column K).

Note: the date are not sorted in ascending order.

enter image description here

Code:

Sub outputfile()
'Capture the contract no. in ContractNo() array
Dim ContractNo() As String
'Capture the project title in ProjectTitle() array
Dim ProjectTitle() As String
'Capture the contract start in ContractStart() array
Dim ContractStart() As Date
'Capture the contract end in ContractEnd() array
Dim ContractEnd() As Date

'Capture ASPQ Cement in ASPQC() array
Dim ASPQC() As Double
'Capture ASP Sand in ASPQS() array
Dim ASPQS() As Double
'Capture ASP Aggregate in ASPQA() array
Dim ASPQA() As Double

i = 2
Do Until IsEmpty(Cells(i, 1).Value)
i = i + 1
Loop

ReDim ContractNo(1 To i - 2)
ReDim ProjectTitle(1 To i - 2)
ReDim ContractStart(1 To i - 2)
ReDim ContractEnd(1 To i - 2)
ReDim ASPQC(1 To i - 2)
ReDim ASPQS(1 To i - 2)
ReDim ASPQA(1 To i - 2)

For i = 1 To UBound(ContractNo, 1)
ContractNo(i) = Cells(i + 1, 1).Value
ProjectTitle(i) = Cells(i + 1, 2).Value
ContractStart(i) = Cells(i + 1, 11).Value
ContractEnd(i) = Cells(i + 1, 12).Value
ASPQC(i) = Cells(i + 1, 14).Value
ASPQS(i) = Cells(i + 1, 15).Value
ASPQA(i) = Cells(i + 1, 16).Value
Next i

End sub

Upvotes: 0

Views: 422

Answers (1)

glh
glh

Reputation: 4972

I believe the best is a built in formula in a cell. 

=TEXT(MIN(K:K), "MMMYY") 

Upvotes: 3

Related Questions