I need to create a spreadsheet with these functions. Can someone help me?

Jhall5388 · Aug 18, 2010

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5Crccestu1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5Crccestu1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5Crccestu1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:Wingdings; panose-1:5 0 0 0 0 0 0 0 0 0; mso-font-charset:2; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:0 268435456 0 0 -2147483648 0;} @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-520092929 1073786111 9 0 415 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:.5in; mso-add-space:auto; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} p.MsoListParagraphCxSpFirst, li.MsoListParagraphCxSpFirst, div.MsoListParagraphCxSpFirst {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; mso-style-type:export-only; margin-top:0in; margin-right:0in; margin-bottom:0in; margin-left:.5in; margin-bottom:.0001pt; mso-add-space:auto; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} p.MsoListParagraphCxSpMiddle, li.MsoListParagraphCxSpMiddle, div.MsoListParagraphCxSpMiddle {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; mso-style-type:export-only; margin-top:0in; margin-right:0in; margin-bottom:0in; margin-left:.5in; margin-bottom:.0001pt; mso-add-space:auto; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} p.MsoListParagraphCxSpLast, li.MsoListParagraphCxSpLast, div.MsoListParagraphCxSpLast {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; mso-style-type:export-only; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:.5in; mso-add-space:auto; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} /* List Definitions */ @list l0 {mso-list-id:2130976038; mso-list-type:hybrid; mso-list-template-ids:1256109302 67698703 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;} @list l0:level1 {mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-.25in;} ol {margin-bottom:0in;} ul {margin-bottom:0in;} --> </style> <o

> </o

>
I want an Excel Worksheet that does the following:
1. Allows an input for the number of periods in Cell B5 (up to 100; any number above 100 results in a warning statement in Cell C2 telling you that you have exceeded the allowable (100) maximum number of periods for the worksheet
2. Only the stated number of periods shows on the worksheet. 10 periods in Cell B5 shows up as 10 periods in Column D; 45 periods in Cell B5 allows 45 periods to show in Column D. If B5 has no input, Column D and E are blank.
3. The amount of Cash flow is inputted in Cell B6. That amount only shows up in Column E for the correct number of periods as stated in Cell B5.
4. Cell G4 sums up all the cash flows shown in the worksheet in Column E.
5. Protect the worksheet, allowing for input in Cells B5 and B6 only.
6. Create a macro button that clears the data from Cells B5 and B6 (and therefore clears Columns D and E automatically).
7. There is never any cash flow in period 0. Period 0 is today, right now. All cash flows occur in the future.
<o

> </o

>

<o

> </o

>

bertie · Aug 18, 2010

This screenshot illustrates the assumptions I made in coding your problem.

The amount of Cash flow is inputted in Cell B6. That amount only shows up in Column E for the correct number of periods as stated in Cell B5<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

This is ambiguous.
What is cash flow?
If B5 = Periods = 5
And B6 = CashFlow = 10
Does this mean E1:E5 =10? As you can see, this is the assumption I made in writing the code.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 116px"><COL style="WIDTH: 64px"><COL style="WIDTH: 224px"><COL style="WIDTH: 64px"><COL style="WIDTH: 80px"><COL style="WIDTH: 116px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="FONT-WEIGHT: bold">Period</TD><TD style="FONT-WEIGHT: bold">Cash Flow</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">-</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="FONT-WEIGHT: bold">Warning</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0.00</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">-</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">10.00</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">-</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">10.00</TD><TD style="FONT-WEIGHT: bold">Total Cash Flow</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">50.00</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-WEIGHT: bold"># Periods</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">10.00</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">-</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-WEIGHT: bold">Cash Flow</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">10.00</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">10.00</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">-</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">10.00</TD><TD style="TEXT-ALIGN: center">-</TD><TD style="TEXT-ALIGN: center">-</TD></TR></TBODY></TABLE>

Allows an input for the number of periods in Cell B5 (up to 100; any number above 100 results in a warning statement in Cell C2 telling you that you have exceeded the allowable (100) maximum number of periods for the worksheet.<o></o>

You can restrict user input using data validation. This will also negate the need for an error message in C2 and reduce the amout of code to process, e.g., <o

></o

>
<o

></o

>

Highlight cell B5<o></o>
Click Data => Validation<o></o>
<o></o>
Settings Tab, Allow drop down box, Select Whole Number<o></o>
Set Min = 1, Max = 100<o></o>
<o></o>
Input Message Tab [optional], Prompt the user which values are allowed<o></o>
<o></o>
Error Alert Tab [optional], Prompt the user which values are allowed<o></o>
<o></o>
Repeat for Cash Flow in B6

If you don’t want to use this method you will need to write error handling code in the event the user leaves B5 or B6 empty or they enter a non-numeric value.

Code:

[COLOR=green]'***************************************************[/COLOR]
   [COLOR=green]'IF YOU USE DATA VALIDATION THIS CODE CAN BE DELETED[/COLOR]
   [COLOR=green]'[/COLOR]
   'check numeric values have been entered
   [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsNumeric(numPeriods) [COLOR=darkblue]Or[/COLOR] numPeriods = 0 [COLOR=darkblue]Then[/COLOR]
      MsgBox "Enter numeric values for number of Periods."
      Range("B5").ClearContents
      [COLOR=darkblue]GoTo[/COLOR] exitProcess
   [COLOR=darkblue]ElseIf[/COLOR] [COLOR=darkblue]Not[/COLOR] IsNumeric(cashFlow) [COLOR=darkblue]Or[/COLOR] cashFlow = 0 [COLOR=darkblue]Then[/COLOR]
      MsgBox "Enter numeric values for Cash Flow."
      Range("B6").ClearContents
      [COLOR=darkblue]GoTo[/COLOR] exitProcess
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=green]'****************************************************[/COLOR]

I have placed Protect and Unprotect the worksheet inside their own procedures. To use you simply call the procedures. For testing I have commented these out.

For example, to reset the cells, the code behind the command button, named "cmdReset", is:

Code:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdReset_Click()
   [COLOR=green]'UnprotectWorksheet[/COLOR]
      Range("[COLOR=red]B5:B6[/COLOR],[COLOR=darkorange]D3:E102[/COLOR],[COLOR=magenta]C2,G4[/COLOR]").ClearContents
   [COLOR=green]'ProtectWorksheet[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

NB You may have to edit the cells to be cleared.

I have used a second command button named, "cmdProcess" to populate columns D and E.

To use the code.
I have assumed the data to be processed is on Sheet1 as are the two command buttons, named, "cmdReset" and "cmdProcess".

Press Alt+F11 to open the VBA Editor
Double click the Sheet1 module in the Project Window
Copy and place the code below. After testing you can uncomment the Protect and Unprotect procedure calls.

Code:

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdReset_Click()
   [COLOR=green]'UnprotectWorksheet[/COLOR]
      Range("B5:B6,D3:E102,C2,G4").ClearContents
   [COLOR=green]'ProtectWorksheet[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ProtectWorksheet()
   [COLOR=darkblue]With[/COLOR] Worksheets("[COLOR=red]Sheet1[/COLOR]")
      .Range("A1:G105").Locked = [COLOR=darkblue]True[/COLOR]
      .Range("B5:B6").Locked = [COLOR=darkblue]False[/COLOR]
      .Protect Password:="[COLOR=red]password[/COLOR]"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UnprotectWorksheet()
   [COLOR=darkblue]With[/COLOR] Worksheets("[COLOR=red]Sheet1[/COLOR]")
      .Unprotect Password:="[COLOR=red]password[/COLOR]"
      .Range("A1:G105").Locked = [COLOR=darkblue]False[/COLOR]
      .Range("B5:B6").Locked = [COLOR=darkblue]False[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdProcess_Click()
   [COLOR=darkblue]Dim[/COLOR] numPeriods [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] cashFlow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
 
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] exitProcess
 
   [COLOR=green]'==================[/COLOR]
   [COLOR=green]'UnprotectWorksheet[/COLOR]
   [COLOR=green]'==================[/COLOR]
 
   numPeriods = Range("B5").Value
   cashFlow = Range("B6").Value
 
   [COLOR=green]'***************************************************[/COLOR]
   [COLOR=green]'IF YOU USE DATA VALIDATION THIS CODE CAN BE DELETED[/COLOR]
   [COLOR=green]'[/COLOR]
   'check numeric values have been entered
   [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsNumeric(numPeriods) [COLOR=darkblue]Or[/COLOR] numPeriods = 0 [COLOR=darkblue]Then[/COLOR]
      MsgBox "Enter numeric values for number of Periods."
      Range("B5").ClearContents
      [COLOR=darkblue]GoTo[/COLOR] exitProcess
   [COLOR=darkblue]ElseIf[/COLOR] [COLOR=darkblue]Not[/COLOR] IsNumeric(cashFlow) [COLOR=darkblue]Or[/COLOR] cashFlow = 0 [COLOR=darkblue]Then[/COLOR]
      MsgBox "Enter numeric values for Cash Flow."
      Range("B6").ClearContents
      [COLOR=darkblue]GoTo[/COLOR] exitProcess
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=green]'****************************************************[/COLOR]
   [COLOR=green]'[/COLOR]
   '
   '-------------------------------------------------
   [COLOR=green]'If you use data validation you only need the code[/COLOR]
   [COLOR=green]'in the ELSE part of the IF statement[/COLOR]
   [COLOR=green]'-------------------------------------------------[/COLOR]
   [COLOR=darkblue]If[/COLOR] numPeriods > 100 [COLOR=darkblue]Then[/COLOR]
      Range("C2").Value = "Warning - Number of periods > 100."
      Range("B5").ClearContents
   [COLOR=darkblue]Else[/COLOR]
      [COLOR=green]'populate columns D and E[/COLOR]
      Range("C2").ClearContents
      [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] numPeriods
         Range("D" & 2 + i).Value = i
         Range("E" & 2 + i).Value = cashFlow
      [COLOR=darkblue]Next[/COLOR] i
      [COLOR=green]'populate total cash flow[/COLOR]
      Range("G4").Value = cashFlow * numPeriods
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
 
exitProcess:
   [COLOR=green]'protect worksheet, display error and exit sub[/COLOR]
   [COLOR=green]'=================[/COLOR]
   [COLOR=green]'ProtectWorksheet[/COLOR]
   [COLOR=green]'=================[/COLOR]
   [COLOR=darkblue]If[/COLOR] Err.Number <> 0 Then _
         MsgBox "Error: " & Err.Number & vbCrLf _
         & "Description: " & Err.Description
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

I need to create a spreadsheet with these functions. Can someone help me?

Jhall5388

New Member

Excel Facts

bertie

Well-known Member

Similar threads

Forum statistics

Share this page

I need to create a spreadsheet with these functions. Can someone help me?

Jhall5388

New Member

Excel Facts

bertie

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock