Hello,
I am very new to Excel / VBA scripting so I apologize in advance.
I have a series of stocks (over 20,000 data values), with associated dates, open, close, total stock volumes in corresponding columns.
What I am trying to do is calculate yearly change for each unique variable from close date of (December 31, 2020) and open date of (January 2, 2020). I am trying to do this for each unique variable.
The below first image are the columns... I was able to obtain the unique variables in Column I. I am now having a difficult time figuring out what I need to do to obtain the specific value on the close date and open date for each unique variable (only once) and place it in Column J.
This was my code to obtain my unique variables from Column A.
' Identify variables used/ define variables / code for Column I - Ticker
Dim wb As Workbook
Dim ws As Worksheet
Dim RngI As Range
Set wb = ThisWorkbook
Set ws = wb.Worksheets("A")
Set RngI = ws.Range("I1")
ws.Range("A:A").AdvancedFilter Action:=xlFilterCopy, CopytoRange:=RngI, Unique:=True
' Bold title for Column I - Ticker
With Range("I1")
.Value = "Ticker"
.Font.Bold = True
End With
Now... I am uncertain where to go from here. I have the below... I tried doing a Vlookup first to see if I could even obtain the close date value for each unique variable... but I am getting an error.
' Identify variables/ Define variables / Place into Column J - Track Change
' Code for close date - open date for each unique variable
Dim i As Long
Dim EndRow As Long
Dim OpenDate As Date
Dim CloseDate As Date
EndRow = RngI.End(xlDown).Row
Set RngB = Range("B:F")
OpenDate = DateSerial(2020, 1, 2)
CloseDate = DateSerial(2020, 12, 31)
For i = 2 To EndRow
ws.Cells(i, 10) = Application.WorksheetFunction.VLookup(CloseDate, RngB, 6, 1)
Next i
End Sub
Any assistance is greatly appreciated!!
Regards,
E.
I am very new to Excel / VBA scripting so I apologize in advance.
I have a series of stocks (over 20,000 data values), with associated dates, open, close, total stock volumes in corresponding columns.
What I am trying to do is calculate yearly change for each unique variable from close date of (December 31, 2020) and open date of (January 2, 2020). I am trying to do this for each unique variable.
The below first image are the columns... I was able to obtain the unique variables in Column I. I am now having a difficult time figuring out what I need to do to obtain the specific value on the close date and open date for each unique variable (only once) and place it in Column J.
This was my code to obtain my unique variables from Column A.
' Identify variables used/ define variables / code for Column I - Ticker
Dim wb As Workbook
Dim ws As Worksheet
Dim RngI As Range
Set wb = ThisWorkbook
Set ws = wb.Worksheets("A")
Set RngI = ws.Range("I1")
ws.Range("A:A").AdvancedFilter Action:=xlFilterCopy, CopytoRange:=RngI, Unique:=True
' Bold title for Column I - Ticker
With Range("I1")
.Value = "Ticker"
.Font.Bold = True
End With
Now... I am uncertain where to go from here. I have the below... I tried doing a Vlookup first to see if I could even obtain the close date value for each unique variable... but I am getting an error.
' Identify variables/ Define variables / Place into Column J - Track Change
' Code for close date - open date for each unique variable
Dim i As Long
Dim EndRow As Long
Dim OpenDate As Date
Dim CloseDate As Date
EndRow = RngI.End(xlDown).Row
Set RngB = Range("B:F")
OpenDate = DateSerial(2020, 1, 2)
CloseDate = DateSerial(2020, 12, 31)
For i = 2 To EndRow
ws.Cells(i, 10) = Application.WorksheetFunction.VLookup(CloseDate, RngB, 6, 1)
Next i
End Sub
Any assistance is greatly appreciated!!
Regards,
E.