Prev: Unexpected internal error or not enough memory
Next: protect macro from a different users access
From: Kevryl on 24 Apr 2010 09:50
I wonder if someone can help me with a little code for inclusion in a macro?
It needs to do the following:

“If cell in column D on this row = "Sunday", perform Macro “Sunday”,
otherwise execute macro “Weekday””

Background info:

I run an accounting system on a series of worksheets. The Cash Journal
dynamically applies the week of the year and day of the week (based on the
first date in the financial year) and conditionally formats rows accordingly
to highlight weekends. Macros post the daily cashup sheet to the Cash
Journal and General journal. I am combining them to operate both from the
Cashup sheet and I need that macro to automatically avoid posting to a row in
the Cash Journal that falls on Sunday. So it must makes a decision on the day
and branch to an alternate macro on Sunday. (Both macros will be the same,
except that “Sunday” will jump down 2 more rows (cash register inputs) than
“Weekday” before recreating a range name that acts as a cursor locator.)

Many thanks
Kevryl

From: JLatham on 24 Apr 2010 10:26
Do you have any code written at all yet? First, I'd advise on not using
"Weekday" as the name of a macro (and to a lesser degree I wouldn't use
Sunday either). Perhaps something like "ProcessSundays" and "ProcessWeedays".

But to your specific question:
Sub MakeTheDecision()
If ThisWorkbook.Worksheets("SheetName").Range("D5") = _
"Sunday" Then
ProcessSundays
Else
ProcessWeekdays
End If
End Sub

That code assumes that it is in the same module with the two referenced Subs.

"Kevryl" wrote:

> I wonder if someone can help me with a little code for inclusion in a macro?
> It needs to do the following:
>
> “If cell in column D on this row = "Sunday", perform Macro “Sunday”,
> otherwise execute macro “Weekday””
>
> Background info:
>
> I run an accounting system on a series of worksheets. The Cash Journal
> dynamically applies the week of the year and day of the week (based on the
> first date in the financial year) and conditionally formats rows accordingly
> to highlight weekends. Macros post the daily cashup sheet to the Cash
> Journal and General journal. I am combining them to operate both from the
> Cashup sheet and I need that macro to automatically avoid posting to a row in
> the Cash Journal that falls on Sunday. So it must makes a decision on the day
> and branch to an alternate macro on Sunday. (Both macros will be the same,
> except that “Sunday” will jump down 2 more rows (cash register inputs) than
> “Weekday” before recreating a range name that acts as a cursor locator.)
>
> Many thanks
> Kevryl
>
From: Kevryl on 24 Apr 2010 11:04
Hi JL, thanks for such a fast response.

Yes & No - no code for the Sunday decision but the very lengthy macros to be
combined have been running smoothly every day for about 6 years.

The names: just used Saturday and Sunday for ease of explanation. I shall
take your advice on that. I'm surprised that "ProcessSundays" doesn't need to
be inside quotes or brackets.

One problem remains: "Range("D5")" in your code is absolute, and it needs to
be relative to travel down the sheet. The cursor will be in column E (at
rangename "CJreturn" at the point when it refers to the contents of Column C.
I could use the same name/delete/rename routine that I use for "CJreturn" to
advance the cursor begin point, and address the rangename, but there's
probably a tidier way. Will that work, just substituting (say) the rangename
"daycheck" for "D5" in the code you have given? I reckon it should.

Well, its 1.00am Sunday here in Tasmania and I gotta go home! I presume its
late Saturday afternoon where you are so have a great evening!

Thanks again.

"JLatham" wrote:

> Do you have any code written at all yet? First, I'd advise on not using
> "Weekday" as the name of a macro (and to a lesser degree I wouldn't use
> Sunday either). Perhaps something like "ProcessSundays" and "ProcessWeedays".


>
> But to your specific question:
> Sub MakeTheDecision()
> If ThisWorkbook.Worksheets("SheetName").Range("D5") = _
> "Sunday" Then
> ProcessSundays
> Else
> ProcessWeekdays
> End If
> End Sub
>
> That code assumes that it is in the same module with the two referenced Subs.
>
> "Kevryl" wrote:
>
> > I wonder if someone can help me with a little code for inclusion in a macro?
> > It needs to do the following:
> >
> > “If cell in column D on this row = "Sunday", perform Macro “Sunday”,
> > otherwise execute macro “Weekday””
> >
> > Background info:
> >
> > I run an accounting system on a series of worksheets. The Cash Journal
> > dynamically applies the week of the year and day of the week (based on the
> > first date in the financial year) and conditionally formats rows accordingly
> > to highlight weekends. Macros post the daily cashup sheet to the Cash
> > Journal and General journal. I am combining them to operate both from the
> > Cashup sheet and I need that macro to automatically avoid posting to a row in
> > the Cash Journal that falls on Sunday. So it must makes a decision on the day
> > and branch to an alternate macro on Sunday. (Both macros will be the same,
> > except that “Sunday” will jump down 2 more rows (cash register inputs) than
> > “Weekday” before recreating a range name that acts as a cursor locator.)
> >
> > Many thanks
> > Kevryl
> >
 | 
Pages: 1
Prev: Unexpected internal error or not enough memory
Next: protect macro from a different users access