PDA

View Full Version : [SOLVED] VBA Code for Worst Month from Daily Data



Stallone
02-05-2019, 12:18 AM
Good Morning,
relatively new to VBA, I have a problem getting the worst month return from a series of daily data and values.
Code is not very elegant but working until ReturnMonth(i), meaning I get all values for DateMonth(i) and ValueMonth(i), but the calculation for ReturnMonth(i) always returns -1.
So I missing something in "remembering" of values or loop?

[CODE]
'Worst Month
Function WorstMonth(Dates As Range, Values As Range)

Dim NewRange As Range
Set NewRange = Application.Union(Dates, Values)
Dim i As Integer
Dim n As Integer
Dim B As Long
Dim C As Long
i = 1
A = Application.WorksheetFunction.Min(Dates)
B = Application.WorksheetFunction.Max(Dates)
C = DateDiff("m", A, B)
n = C + 1
Dim j As Integer
Dim FirstDate As Long
Dim DateMonth() As Long
ReDim DateMonth(1 To n)
Dim ValueMonth() As Double
ReDim ValueMonth(1 To n)
Dim ReturnMonth() As Double
ReDim ReturnMonth(1 To n)
FirstDate = Application.WorksheetFunction.Min(Dates)

For i = 1 To n
DateMonth(i) = Application.WorksheetFunction.EOMONTH(FirstDate, i - 1)
ValueMonth(i) = Application.WorksheetFunction.VLookup(DateMonth(i), NewRange, 2)
ReturnMonth(i) = ValueMonth(i) / ValueMonth(i - 1) - 1
Next i

WorstMonth = "?"
End Function
[/CODE ]

Stallone
02-05-2019, 01:23 AM
Ha, got it myself, afer 1,5 days :P
but thx anyway :D



'Worst Month
Function WorstMonth(Dates As Range, Values As Range)

Dim NewRange As Range
Set NewRange = Application.Union(Dates, Values)
Dim i As Integer
Dim n As Integer
Dim B As Long
Dim C As Long
i = 1
A = Application.WorksheetFunction.Min(Dates)
B = Application.WorksheetFunction.Max(Dates)
C = DateDiff("m", A, B)
n = C + 1
Dim j As Integer
j = i + 1
M = C
Dim FirstDate As Long
Dim DateMonth() As Long
ReDim DateMonth(1 To n)
Dim ValueMonth() As Double
ReDim ValueMonth(1 To n)
Dim ReturnMonth() As Double
ReDim ReturnMonth(1 To n)
FirstDate = Application.WorksheetFunction.Min(Dates)

For i = 1 To n
DateMonth(i) = Application.WorksheetFunction.EOMONTH(FirstDate, i - 1)
ValueMonth(i) = Application.WorksheetFunction.VLookup(DateMonth(i), NewRange, 2)
Next i
For i = 2 To n
ReturnMonth(i) = ValueMonth(i) / ValueMonth(i - 1) - 1
MsgBox (ReturnMonth(i))
Next i

WorstMonth = Application.WorksheetFunction.Min(ReturnMonth)
End Function