NEWBIE needs BIG help with Macro

[lehr.craig]

Thanks so much in advance for your help. I'm very new to this stuff,
so I probably won't explain myself very well. I'm thinking a macro is
what I need, but I'm not sure.

A3 is the current date.
L9 is the current number of mutual fund units that a client owns
G9 is the amount of money the client invests into the fund every month
on the 5th.
M9 is the current price of the mutual fund.

I need a trigger that will update L9 on the 5th of each month giving
me the new unit total based on the fact that the amount in G9 has now
been invested into the fund.

 

[Incidental]

Hi

You could try something like the code below which should do what you
want. It will pass the current date to a string variable then split
that string into the following sections

MyDay = the numeric value of the day i.e. the 5th
DayName = the three letter day abbreviation

It will then check MyDay every time the workbook is opened if the
value returned is
5 then the 5th is a weekday presuming you will only be opening it
during the five day working week
6 then the 5th may have been a Sunday so check DayName if it is "Mon"
then the 5th was a Sunday run the code if DayName is not "Mon" do
nothing
7 then the 5th may have been a Saturday so check DayName if it is
"Mon" then the 5th was a Saturday run the code if DayName is not "Mon"
do nothing

In the workbook module
Option Explicit
Dim MyDate, DayName As String 'Declare your variables
Dim MyDay As Integer

Private Sub Workbook_Open()

MyDate = Format(Date, "DDD DD/MMM/YYYY") 'Pass the full date to a
string

'MyDate = "Sat 06/04/2007" 'to test this sub, comment the line above
'and remove the comment mark from before MyDate, test your variations

Sheets(1).Activate 'Select the worksheet you want to work with

[A3].Value = Right(MyDate, 11) 'Put only the DD/MMM/YYYY to cell A3

MyDay = Mid(MyDate, 5, 2) 'Pass the numeric value of the day to an
integer

DayName = Left(MyDate, 3) 'Pass the day name to a string

Select Case MyDay 'Set to check the date

Case "5" '5th falls on a week day
[L9].Value = [L9].Value - [G9].Value

Case "6" '5th falls on a Sunday
If DayName = "Mon" Then
[L9].Value = [L9].Value - [G9].Value
End If

Case "7" '5th falls on a Saturday
If DayName = "Mon" Then
[L9].Value = [L9].Value - [G9].Value
End If

End Select

End Sub

hope this is of some help to you

S