asdasdsad
04-21-2020, 12:41 AM
Hi all,
I'm in the process of trying to figure out how to find the max date of a value in column A. From there I need to link up the price/volatility/other variables associated with that date.
26398
I managed to find this code from other forum but it can only do for one column. What if I wish to add more columns after price?
Sub test()
Dim a, i As Long, e, n As Long
a = Sheets("sheet1").Cells(1).CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
.CompareMode = 1: n = 1
For i = 2 To UBound(a, 1)
If a(i, 1) <> "" Then
If Not .exists(a(i, 1)) Then
Set .Item(a(i, 1)) = _
CreateObject("System.Collections.SortedList")
End If
.Item(a(i, 1))(a(i, 2)) = a(i, 3)
End If
Next
For Each e In .keys
n = n + 1: a(n, 1) = e
a(n, 2) = .Item(e).GetKey(.Item(e).Count - 1)
a(n, 3) = .Item(e).GetByIndex(.Item(e).Count - 1)
Next
End With
Sheets("sheet2").Cells(1).Resize(n, 3).Value = a
End Sub
I'm in the process of trying to figure out how to find the max date of a value in column A. From there I need to link up the price/volatility/other variables associated with that date.
26398
I managed to find this code from other forum but it can only do for one column. What if I wish to add more columns after price?
Sub test()
Dim a, i As Long, e, n As Long
a = Sheets("sheet1").Cells(1).CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
.CompareMode = 1: n = 1
For i = 2 To UBound(a, 1)
If a(i, 1) <> "" Then
If Not .exists(a(i, 1)) Then
Set .Item(a(i, 1)) = _
CreateObject("System.Collections.SortedList")
End If
.Item(a(i, 1))(a(i, 2)) = a(i, 3)
End If
Next
For Each e In .keys
n = n + 1: a(n, 1) = e
a(n, 2) = .Item(e).GetKey(.Item(e).Count - 1)
a(n, 3) = .Item(e).GetByIndex(.Item(e).Count - 1)
Next
End With
Sheets("sheet2").Cells(1).Resize(n, 3).Value = a
End Sub