sourabh_ajmera
New Member
- Joined
- Jul 17, 2014
- Messages
- 36
Hello everybody,
I have an excel datasheet where there are dates in column and some data corresponding to the date in it. Each column only consists of a single date as header (always in row 1)
Now, I would to add a monthly average as the dates keep getting added in the datasheet on daily basis.
For example if the date is 9/1/2014 (september 1st) I would like to add a column of 'Sept-14' where I would see the avg of all sept dates.
Next month when the date 10/1/2014 (oct 1st) is added I would like the Oct-14 column to be added.
i.e for every 1st day of the month I would like to add a month column where i can avg all the month dates
But i dont want it to find only for 2014...in future it should work for 2015,2016 and so on as it is just addition of month
so i have tried something like this. but its incomplete
Please help. Thank you in advance
I have an excel datasheet where there are dates in column and some data corresponding to the date in it. Each column only consists of a single date as header (always in row 1)
Now, I would to add a monthly average as the dates keep getting added in the datasheet on daily basis.
For example if the date is 9/1/2014 (september 1st) I would like to add a column of 'Sept-14' where I would see the avg of all sept dates.
Next month when the date 10/1/2014 (oct 1st) is added I would like the Oct-14 column to be added.
i.e for every 1st day of the month I would like to add a month column where i can avg all the month dates
But i dont want it to find only for 2014...in future it should work for 2015,2016 and so on as it is just addition of month
so i have tried something like this. but its incomplete
Code:
Dim FindMon As Range
Dim MonCol As Integer
Dim MonColtxt As String
Set FindMon = Rows(1).Find(What:="Jan", LookIn:=xlValues, LookAt:=xlWhole)
MonCol = FindMon.Column
MonColtxt = Replace(Cells(1, MonCol).Address(False, False), "1", "")
If (MonColtxt = Jan) Then 'To find if that specific month column already exists or not
End If
Else
Set FindDate = Rows(1).Find(What:="1/1*", LookIn:=xlValues, LookAt:=xlWhole)
If (FindDate = 1 / 1 *) Then '<-- Here is where I want it to find the month but without the year..just the month and then add the resp month colum at the end
Please help. Thank you in advance