A12 = Microsoft


O12 = the market value of my microsoft shares.
M12 = the number of Microsoft shares I currently own
N12 = the current price of each share.
G12 = the amount of money I invest into Microsoft each month ...
always the same monthly investment
H12 = the day of the month (call it the 1st) that I make the monthly
investment .... always the same day .... unless it is not a business
day

I need a formula or macro that would re-calculate cell M12 after I
make each monthly investment.


Summary.... on May 31st, I owned 4000 Microsoft shares. On the 1st I
bought 100 more. I need M12 to update to 4100 when I run the macro.
Can the macro be programmed to run only once each month?

I see that you have posted a number of inquiries for this and I have given it
a bit of thought and come up with the following macro code. I am assuming
that your knowledge is limited so I have tried to keep it as simple as
possible so that hopefully you can understand it.

It needs to go in a procedure called Auto_Open so that it will automatically
run when you open the workbook. This is simpler than using events.

You will see that the date of the last update needs to be in a cell on the
worksheet. You should initialize this yourself before running the macro.

The MsgBoxes are to simply confirm what is happening in the macro while
testing. You can simply comment them out after testing if you don't want them.

Sub Auto_Open()

'For this macro info is on sheet1 at the following addresses.

'B2 is the number of additionsl shares purchased each month
'C2 is a number for the day of the month for update to occur
'D2 is the total number of shares to date
'G2 is the date the last update occurred

Dim lastUpdate As String
Dim checkDay As Integer
Dim currentMonth As String
Dim updateDay As Integer

Sheets("Sheet1").Activate

'Assign the update day of month to a variable
updateDay = Range("C2")

'Assign the todays day of month to a variable
checkDay = Val(Format(Date, "dd"))

'Assign current month to a variable
currentMonth = Format(Date, "mmm")

'Assign last update month to a variable
lastUpdate = Format(Range("G2"), "mmm")

If checkDay = updateDay Then 'Day to update data
If currentMonth = lastUpdate Then
MsgBox "Data has already been updated this month"
Else
Range("D2") = Range("D2") + Range("B2")
Range("G2") = Date 'Save last update day
MsgBox "Data has been updated"
End If
Else
MsgBox "Not the day of month to update data"
End If


End Sub

I hope this gives you some pointers on how to go about achieving your goal.

Regards,

OssieMac


"(E-Mail Removed)" wrote:

> A12 = Microsoft
>
>
> O12 = the market value of my microsoft shares.
> M12 = the number of Microsoft shares I currently own
> N12 = the current price of each share.
> G12 = the amount of money I invest into Microsoft each month ...
> always the same monthly investment
> H12 = the day of the month (call it the 1st) that I make the monthly
> investment .... always the same day .... unless it is not a business
> day
>
> I need a formula or macro that would re-calculate cell M12 after I
> make each monthly investment.
>
>
> Summary.... on May 31st, I owned 4000 Microsoft shares. On the 1st I
> bought 100 more. I need M12 to update to 4100 when I run the macro.
> Can the macro be programmed to run only once each month?
>
>

I should have added that the macro is a bit simplistic and requires you to
open it on the day of the month that the update is to occur. Perhaps now you
have some pointers you can look at what is required to enhance it to update
on other days if the specific day is missed. As a leader, you need to compare
the current month with the month of the last update first.

Regards,

OssieMac

"OssieMac" wrote:

> I see that you have posted a number of inquiries for this and I have given it
> a bit of thought and come up with the following macro code. I am assuming
> that your knowledge is limited so I have tried to keep it as simple as
> possible so that hopefully you can understand it.
>
> It needs to go in a procedure called Auto_Open so that it will automatically
> run when you open the workbook. This is simpler than using events.
>
> You will see that the date of the last update needs to be in a cell on the
> worksheet. You should initialize this yourself before running the macro.
>
> The MsgBoxes are to simply confirm what is happening in the macro while
> testing. You can simply comment them out after testing if you don't want them.
>
> Sub Auto_Open()
>
> 'For this macro info is on sheet1 at the following addresses.
>
> 'B2 is the number of additionsl shares purchased each month
> 'C2 is a number for the day of the month for update to occur
> 'D2 is the total number of shares to date
> 'G2 is the date the last update occurred
>
> Dim lastUpdate As String
> Dim checkDay As Integer
> Dim currentMonth As String
> Dim updateDay As Integer
>
> Sheets("Sheet1").Activate
>
> 'Assign the update day of month to a variable
> updateDay = Range("C2")
>
> 'Assign the todays day of month to a variable
> checkDay = Val(Format(Date, "dd"))
>
> 'Assign current month to a variable
> currentMonth = Format(Date, "mmm")
>
> 'Assign last update month to a variable
> lastUpdate = Format(Range("G2"), "mmm")
>
> If checkDay = updateDay Then 'Day to update data
> If currentMonth = lastUpdate Then
> MsgBox "Data has already been updated this month"
> Else
> Range("D2") = Range("D2") + Range("B2")
> Range("G2") = Date 'Save last update day
> MsgBox "Data has been updated"
> End If
> Else
> MsgBox "Not the day of month to update data"
> End If
>
>
> End Sub
>
> I hope this gives you some pointers on how to go about achieving your goal.
>
> Regards,
>
> OssieMac
>
>
> "(E-Mail Removed)" wrote:
>
> > A12 = Microsoft
> >
> >
> > O12 = the market value of my microsoft shares.
> > M12 = the number of Microsoft shares I currently own
> > N12 = the current price of each share.
> > G12 = the amount of money I invest into Microsoft each month ...
> > always the same monthly investment
> > H12 = the day of the month (call it the 1st) that I make the monthly
> > investment .... always the same day .... unless it is not a business
> > day
> >
> > I need a formula or macro that would re-calculate cell M12 after I
> > make each monthly investment.
> >
> >
> > Summary.... on May 31st, I owned 4000 Microsoft shares. On the 1st I
> > bought 100 more. I need M12 to update to 4100 when I run the macro.
> > Can the macro be programmed to run only once each month?
> >
> >

Put ALL of this in your ThisWorkbook module. Change sheet2 to your sheet
name. Fire the setwhichmonth to test or reset.

'============
Public whichmonth

Sub setwhichmoth()
whichmonth = 1
End Sub
'=========
Sub workbook_open()
On Error GoTo endit
If Month(Date) > whichmonth Then nm = Month(Date)
If DateSerial(Year(Date), nm, 1) >= _
Application.Run("ATPVBAEN.xla!Workday", nm - Day(nm), 1) Then
With Sheets("sheet2").Range("k12")
.Value = .Value + 100
End With
whichmonth = Month(Date)
End If
endit:
End Sub
'=======
--
Don Guillett
SalesAid Software
(E-Mail Removed)
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> A12 = Microsoft
>
>
> O12 = the market value of my microsoft shares.
> M12 = the number of Microsoft shares I currently own
> N12 = the current price of each share.
> G12 = the amount of money I invest into Microsoft each month ...
> always the same monthly investment
> H12 = the day of the month (call it the 1st) that I make the monthly
> investment .... always the same day .... unless it is not a business
> day
>
> I need a formula or macro that would re-calculate cell M12 after I
> make each monthly investment.
>
>
> Summary.... on May 31st, I owned 4000 Microsoft shares. On the 1st I
> bought 100 more. I need M12 to update to 4100 when I run the macro.
> Can the macro be programmed to run only once each month?
>