CharlieFly
10-19-2017, 04:48 AM
Hey Team,
First post here so hope this is all in the right place and everything!
I'm trying to create a VBA function that looks up a relevant date (specifically year) I.e. "2017" and then takes this information and sums up all the prices of a stock that correspond to this date. This is simple enough in excel but I'm not sure how to create it such that I can easily vary the incoming year or incoming worksheet that I want to refer to with the macro. My code is as follows:
Dim testform As String
Dim rng As String
Dim rngend As String
Yr = "2017"
If Yr = "2017" Then
rng = ">01/01/2017"
rngend = "<=31/12/2017"
End If
testform = "=SUMIFS(B2:B#,A2:A#,"">01/01/2017"",A2:A#,""<=31/12/2017"")"
lr = Range("A" & Rows.Count).End(xlUp).Row
testform = Replace(frmla, "#", lr, 1, -1, 1)
Range("R1").Formula = testform
Essentially this work but it doesn't allow me the ability to change the test range of dates ">01/01/2017" nor am I sure how to insert a relevant sheet into this function (i.e. sheet test!B2:B3)
I hope this is clear what I'm trying to do, let me know if I need to explain further!
Kind Regards,
Charlie
First post here so hope this is all in the right place and everything!
I'm trying to create a VBA function that looks up a relevant date (specifically year) I.e. "2017" and then takes this information and sums up all the prices of a stock that correspond to this date. This is simple enough in excel but I'm not sure how to create it such that I can easily vary the incoming year or incoming worksheet that I want to refer to with the macro. My code is as follows:
Dim testform As String
Dim rng As String
Dim rngend As String
Yr = "2017"
If Yr = "2017" Then
rng = ">01/01/2017"
rngend = "<=31/12/2017"
End If
testform = "=SUMIFS(B2:B#,A2:A#,"">01/01/2017"",A2:A#,""<=31/12/2017"")"
lr = Range("A" & Rows.Count).End(xlUp).Row
testform = Replace(frmla, "#", lr, 1, -1, 1)
Range("R1").Formula = testform
Essentially this work but it doesn't allow me the ability to change the test range of dates ">01/01/2017" nor am I sure how to insert a relevant sheet into this function (i.e. sheet test!B2:B3)
I hope this is clear what I'm trying to do, let me know if I need to explain further!
Kind Regards,
Charlie