Running Totals

[Dolphinboi]

Hello All:
I work for a hotel, and every night the auditor has to fill out a hand written sheet. I want to know if there is a formula I can use to keep a running total of the month to date revenue using two columns. example

A:1= Today Revenue
B:1= Running total revenue.

Any help would be greatly appreciated.

 

[Yogi Anand]

Hi Guys:

I think what Dolphinboi is doing is enter Daily Revenue in cell B7, and the monthly total is in cell D7.

Now formula-wise we are saying
that every night ... (New) D7 = (old) D7 + B7

normal worksheet working will create circular reference problem.

One then has to write a WorkSheet_SelectionChange event that when the value of B7 changes, update D7 by adding to it the value of b7. Agree?

So, the code would be


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
[D7]=[D7]+[B7]
End Sub


Regards!

Yogi
This message was edited by Yogi Anand on 2002-10-12 23:29

 

[plettieri]

Yogi and all you code experts are "wizards" in my opinion..we beginners are lucky to have you around....I was trying to give some simple help with a basic formular and simple layout.

pll

 

[Yogi Anand]

On 2002-10-12 23:35, plettieri wrote:
Yogi and all you code experts are "wizards" in my opinion..we beginners are lucky to have you around....I was trying to give some simple help with a basic formular and simple layout.

pll

Oh my goodness plettieri!

The code in this case just one-liner -- in any event, you are so kind, thoughtful and generous with your praise. I look at the board as our cyber-family where we enjoy fire-side chat every day -- what could be more fun!

Regards!

Yogi

 

[Dolphinboi]

[No message]

 

[Dolphinboi]

Yogi has it right, but I'm slightly excel illeterate, how do I do the code and get it to work.

 

[Ivan F Moala]

On 2002-10-13 01:47, Dolphinboi wrote:
Yogi has it right, but I'm slightly excel illeterate, how do I do the code and get it to work.

<META content=Excel.Sheet name=ProgId><META content="Microsoft Excel 9" name=Generator><LINK href="./sht_files/filelist.xml" rel=File-List><STYLE id=AAA_Instructions_VBAEnter_29414_Styles></STYLE>  <DIV id=AAA_Instructions_VBAEnter_29414 align=center xublishsource="Excel"><TABLE style="TABLE-LAYOUT: fixed; WIDTH: 440pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=586 border=0 x:str><COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 320pt; mso-width-source: userset; mso-width-alt: 15616" width=427><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" width=25><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD class=xl3729414 style="WIDTH: 23pt; HEIGHT: 14.25pt" width=30 height=19> </TD><TD class=xl4029414 style="WIDTH: 48pt" width=64> </TD><TD class=xl4029414 style="WIDTH: 320pt" width=427> </TD><TD class=xl4029414 style="WIDTH: 30pt" width=40> </TD><TD class=xl3629414 style="WIDTH: 19pt" width=25> </TD></TR><TR style="HEIGHT: 24.75pt" height=33><TD class=xl2429414 style="HEIGHT: 24.75pt" height=33> </TD><TD class=xl4129414 style="BORDER-RIGHT: white 2pt double" colSpan=3>WorkSheet_SelectionChange()</TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 24pt" height=32><TD class=xl2429414 style="HEIGHT: 24pt" height=32> </TD><TD class=xl3829414>STEP</TD><TD class=xl3929414><SPAN style="mso-spacerun: yes"> </SPAN>'<FONT class=font1029414> </FONT><FONT class=font929414>HOW<SPAN style="mso-spacerun: yes">  </SPAN></FONT><FONT class=font1129414>J<SPAN style="mso-spacerun: yes">  </SPAN></FONT></TD><TD class=xl3829414>OK</TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2429414 style="HEIGHT: 15pt" height=20> </TD><TD class=xl3329414 x:num>1</TD><TD class=xl3029414 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes"> </SPAN>Press <FONT class=font829414>Alt-F11</FONT><FONT class=font529414> to display the Visual Basic editor (VBE).</FONT></TD><TD class=xl3529414 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2429414 style="HEIGHT: 15pt" height=20> </TD><TD class=xl2529414><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2529414><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3429414> </TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 51pt" height=68><TD class=xl2429414 style="HEIGHT: 51pt" height=68> </TD><TD class=xl3329414 x:num>2</TD><TD class=xl3029414 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes"> </SPAN>In the <FONT class=font829414>Project Explorer window</FONT><FONT class=font529414>, find an entry for the sheet you're working with. (For example, look for Sheet1 if the cells you're formatting are on that sheet.) If you don't see the </FONT><FONT class=font829414>Project Explorer window</FONT><FONT class=font529414>, press </FONT><FONT class=font829414>Ctrl-R</FONT><FONT class=font529414>.</FONT></TD><TD class=xl3529414 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2429414 style="HEIGHT: 15pt" height=20> </TD><TD class=xl2529414><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2529414><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3429414> </TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl2429414 style="HEIGHT: 25.5pt" height=34> </TD><TD class=xl3329414 x:num>3</TD><TD class=xl3029414 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes"> </SPAN>Double-click the sheet entry in the <FONT class=font829414>Project Explorer window</FONT><FONT class=font529414>. Eg if working on Sheet1 then double click Sheet1.</FONT></TD><TD class=xl3529414 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2429414 style="HEIGHT: 15pt" height=20> </TD><TD class=xl2529414><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2529414><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3429414> </TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl2429414 style="HEIGHT: 38.25pt" height=51> </TD><TD class=xl3329414 x:num>4</TD><TD class=xl3029414 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes"> </SPAN>Above the code window, you'll find two drop-down lists. In the list on the left, choose <FONT class=font829414>Worksheet</FONT><FONT class=font529414>. In the list on the right, choose </FONT><FONT class=font829414>SelectionChange</FONT><FONT class=font529414>.</FONT></TD><TD class=xl3529414 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2429414 style="HEIGHT: 15pt" height=20> </TD><TD class=xl2529414><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2529414><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3429414> </TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl2429414 style="HEIGHT: 38.25pt" height=51> </TD><TD class=xl3329414 x:num>5</TD><TD class=xl3029414 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes"> </SPAN>A procedure template for <FONT class=font829414>Worksheet_SelectionChange</FONT><FONT class=font529414> now appears in the code window. Between the </FONT><FONT class=font829414>Private Sub</FONT><FONT class=font529414> line and the </FONT><FONT class=font829414>End Sub</FONT><FONT class=font529414> line, type in your procedure.</FONT></TD><TD class=xl3529414 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2429414 style="HEIGHT: 15pt" height=20> </TD><TD class=xl2529414><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2529414><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3429414> </TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl2429414 style="HEIGHT: 25.5pt" height=34> </TD><TD class=xl3329414>eg.</TD><TD class=xl3029414 style="BORDER-LEFT: medium none; WIDTH: 320pt" width=427>For example your <FONT class=font829414>Worksheet_SelectionChange</FONT><FONT class=font529414> procedure would look like this:</FONT></TD><TD class=xl3529414 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2429414 style="HEIGHT: 15pt" height=20> </TD><TD class=xl2329414> </TD><TD class=xl2529414><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3429414> </TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl2429414 style="HEIGHT: 15pt" height=20> </TD><TD class=xl3329414>Code:</TD><TD class=xl3129414 style="WIDTH: 320pt" width=427>Private Sub<FONT class=font529414> </FONT><FONT class=font629414>Worksheet_SelectionChange </FONT><FONT class=font529414>(</FONT><FONT class=font729414>ByVal</FONT><FONT class=font529414> </FONT><FONT class=font629414>Target </FONT><FONT class=font729414>As</FONT><FONT class=font529414> </FONT><FONT class=font629414>Range</FONT><FONT class=font529414>)</FONT></TD><TD class=xl3529414 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2429414 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2329414> </TD><TD class=xl2829414 style="WIDTH: 320pt" width=427><SPAN style="mso-spacerun: yes">   </SPAN>[D7]=[D7]+[B7]</TD><TD class=xl2229414> </TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2429414 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2729414> </TD><TD class=xl4429414 style="WIDTH: 320pt" width=427>End Sub</TD><TD class=xl2629414> </TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2429414 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2329414> </TD><TD class=xl2829414 style="WIDTH: 320pt" width=427> </TD><TD class=xl2229414> </TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2429414 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2729414> </TD><TD class=xl2929414 style="WIDTH: 320pt" width=427> </TD><TD class=xl2629414> </TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl2429414 style="HEIGHT: 13.5pt" height=18> </TD><TD class=xl2429414> </TD><TD class=xl2429414> </TD><TD class=xl2429414> </TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 30.75pt" height=41><TD class=xl2429414 style="HEIGHT: 30.75pt" height=41> </TD><TD class=xl2429414> </TD><TD class=xl3229414 style="WIDTH: 320pt" width=427><SUP>µ</SUP><FONT class=font1329414> HTH - Ivan F Moala </FONT><FONT class=font1229414><SUP>µ</SUP></FONT></TD><TD class=xl2429414> </TD><TD class=xl2429414> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl2429414 style="HEIGHT: 13.5pt" height=18> </TD><TD class=xl2429414> </TD><TD class=xl2429414> </TD><TD class=xl2429414> </TD><TD class=xl2429414> </TD></TR></TBODY></TABLE></DIV>

 

[Dolphinboi]

I did that , but I get a run-time error 13:

 

[Ivan F Moala]

On 2002-10-13 03:22, Dolphinboi wrote:
I did that , but I get a run-time error 13:

I actually wouldn't use the Selection change event .....

The Worksheet_Change would be better as the selection change event will evaluate your Data when ever you move/change cell location.

Better off with this;

<pre/>
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$7" Then
If Not IsNumeric(Target) Then Exit Sub
Application.EnableEvents = False
[D7] = [D7] + [B7]
Application.EnableEvents = True
End If
End Sub
</pre>


BTW, what did were you doing just before the Error ?? you may have entered an alpha character ?? [Space]

 

[Aladin Akyurek]

On 2002-10-13 03:40, Ivan F Moala wrote:

On 2002-10-13 03:22, Dolphinboi wrote:
I did that , but I get a run-time error 13:

I actually wouldn't use the Selection change event .....

The Worksheet_Change would be better as the selection change event will evaluate your Data when ever you move/change cell location.

Better off with this;

<pre/>
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$7" Then
If Not IsNumeric(Target) Then Exit Sub
Application.EnableEvents = False
[D7] = [D7] + [B7]
Application.EnableEvents = True
End If
End Sub
</pre>


BTW, what did were you doing just before the Error ?? you may have entered an alpha character ?? [Space]

Indeed...

Is it not equivalent to...

=RECALL(B7)+B7

to be entered in D7.

RECALL requires that you have installed the morefunc.xll add-in.

Aladin

 

[Dolphinboi]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$7" Then
If Not IsNumeric(Target) Then Exit Sub
Application.EnableEvents = False
[D7] = [D7] + [B7]
Application.EnableEvents = True
End If
End Sub

Didnt' seem to work either...I'm lost LOL