Download the file sales_data.xlsx and complete the following task. Open the file and save it as...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Download the file sales_data.xlsx and complete the following task. Open the file and save it as a Macro Enabled Workbook (file extension .xlsm). Name this spreadsheet with your network login followed by 104.xlsm. For example, if your network login was barn4520, then the file should be named barn4520_104.xlsm. Remember to save your work frequently in case there are problems. 1. In the given file, write a subroutine that adds a worksheet named Formula List after the original worksheet, and then goes through the original worksheet hunting for cells with formulas. Each time it finds a formula, it records information about it in a new row of the Formula List worksheet. Specifically, it records the worksheet's name in column A, the formula as a string in column B, and the formula's value in column C. Hints: o To check whether a cell contains a formula, use the range property Has Formula. o To check all the cells that are being used, use of the worksheet property UsedRange. o To write a formula into a cell as a string, put an apostrophe () in front of the string when you write it to the cell as a value. For example, to make the formula appear in a cell instead of Excel trying to execute the formula, I could type '=sum(A3:A6) into the cell. o To add a worksheet to the end of the list of worksheets you could use the following statement: Set ws New = Worksheets.Add(after: Worksheets (Worksheets.count)) O To automatically make columns fit the widest text they contain, use the Autofit property. wsNew. Range("A:C").Columns.AutoFit Note You have to use Button(s) on worksheet(s) to run the task(s) in this lab and all next labs. Marks will be deducted if you do not use Buttons A Region 1 2 3 4 Jan 5 Feb 6 Mar 7 Apr 8 May 9 Jun 10 Jul 11 Aug 12 Sep 13 Oct 14 Nov 15 Dec 16 7 Totals: 18 Average: 9 B $ $ $ $ $ D Product 1 Product 2 Product 3 Product 4 $ $ 3,845.00 $ 4,224.00 $ $ 3,487.00 $ 1,561.00 $ 2,614.00 $ 2,411.00 3,487.00 $ 2,761.00 $ 1,537.00 $ 1,600.00 3,647.00 $ 4,045.00 4,841.00 $ 1,680.00 1,165.00 $ 3,943.00 2,893.00 $ 4,869.00 1,270.00 $ 2,372.00 3,299.00 $ 1,204.00 2,127.00 $ 1,553.00 $ 2,095.00 $ 2,233.00 $ 1,911.00 $ 3,633.00 $ 1,366.00 $ 2,410.00 $ 2,167.00 $ 2,120.00 $ E $ 1,338.00 $ 2,761.00 $ 2,875.00 $ $ 4,817.00 $ 3,250.00 $ 2,802.00 $ 1,217.00 1,160.00 $ 1,559.00 $ 3,119.00 $ 2,334.00 $ 4,747.00 $ 1,497.00 $ 3,445.00 $ 4,254.00 $ 3,920.00 $ 32,462.00 $ 30,304.00 $ 33,531.00 $ 33,168.00 $ 2,705.17 $ 2,525.33 $ 2,794.25 $ 2,764.00 F G Sum $ 10,073.00 $ $ 9,385.00 $ $ 15,761.00 $ $ 10,201.00 $ $ 9,436.00 $ $ 12,083.00 $ $ 8,641.00 $ $ 8,790.00 $ $ 8,191.00 $ $ 12,029.00 $ $ 11,759.00 $ $ 13,116.00 $ H Average 2,518.25 2,346.25 3,940.25 2,550.25 2,359.00 3,020.75 2,160.25 2,197.50 2,047.75 3,007.25 2,939.75 3,279.00 $ 129,465.00 $ 10,788.75 $ 2,697.19 K Download the file sales_data.xlsx and complete the following task. Open the file and save it as a Macro Enabled Workbook (file extension .xlsm). Name this spreadsheet with your network login followed by 104.xlsm. For example, if your network login was barn4520, then the file should be named barn4520_104.xlsm. Remember to save your work frequently in case there are problems. 1. In the given file, write a subroutine that adds a worksheet named Formula List after the original worksheet, and then goes through the original worksheet hunting for cells with formulas. Each time it finds a formula, it records information about it in a new row of the Formula List worksheet. Specifically, it records the worksheet's name in column A, the formula as a string in column B, and the formula's value in column C. Hints: o To check whether a cell contains a formula, use the range property Has Formula. o To check all the cells that are being used, use of the worksheet property UsedRange. o To write a formula into a cell as a string, put an apostrophe () in front of the string when you write it to the cell as a value. For example, to make the formula appear in a cell instead of Excel trying to execute the formula, I could type '=sum(A3:A6) into the cell. o To add a worksheet to the end of the list of worksheets you could use the following statement: Set ws New = Worksheets.Add(after: Worksheets (Worksheets.count)) O To automatically make columns fit the widest text they contain, use the Autofit property. wsNew. Range("A:C").Columns.AutoFit Note You have to use Button(s) on worksheet(s) to run the task(s) in this lab and all next labs. Marks will be deducted if you do not use Buttons A Region 1 2 3 4 Jan 5 Feb 6 Mar 7 Apr 8 May 9 Jun 10 Jul 11 Aug 12 Sep 13 Oct 14 Nov 15 Dec 16 7 Totals: 18 Average: 9 B $ $ $ $ $ D Product 1 Product 2 Product 3 Product 4 $ $ 3,845.00 $ 4,224.00 $ $ 3,487.00 $ 1,561.00 $ 2,614.00 $ 2,411.00 3,487.00 $ 2,761.00 $ 1,537.00 $ 1,600.00 3,647.00 $ 4,045.00 4,841.00 $ 1,680.00 1,165.00 $ 3,943.00 2,893.00 $ 4,869.00 1,270.00 $ 2,372.00 3,299.00 $ 1,204.00 2,127.00 $ 1,553.00 $ 2,095.00 $ 2,233.00 $ 1,911.00 $ 3,633.00 $ 1,366.00 $ 2,410.00 $ 2,167.00 $ 2,120.00 $ E $ 1,338.00 $ 2,761.00 $ 2,875.00 $ $ 4,817.00 $ 3,250.00 $ 2,802.00 $ 1,217.00 1,160.00 $ 1,559.00 $ 3,119.00 $ 2,334.00 $ 4,747.00 $ 1,497.00 $ 3,445.00 $ 4,254.00 $ 3,920.00 $ 32,462.00 $ 30,304.00 $ 33,531.00 $ 33,168.00 $ 2,705.17 $ 2,525.33 $ 2,794.25 $ 2,764.00 F G Sum $ 10,073.00 $ $ 9,385.00 $ $ 15,761.00 $ $ 10,201.00 $ $ 9,436.00 $ $ 12,083.00 $ $ 8,641.00 $ $ 8,790.00 $ $ 8,191.00 $ $ 12,029.00 $ $ 11,759.00 $ $ 13,116.00 $ H Average 2,518.25 2,346.25 3,940.25 2,550.25 2,359.00 3,020.75 2,160.25 2,197.50 2,047.75 3,007.25 2,939.75 3,279.00 $ 129,465.00 $ 10,788.75 $ 2,697.19 K
Expert Answer:
Answer rating: 100% (QA)
Heres the VBA code to complete the task Sub ListFormulas Dim ws As Worksheet Dim rng As Range Dim fw... View the full answer
Related Book For
Integrated Accounting
ISBN: 978-1285462721
8th edition
Authors: Dale A. Klooster, Warren Allen, Glenn Owen
Posted Date:
Students also viewed these programming questions
-
Let's say we wanted to incorporate force sensor data into our experiment. We were already using the eyebolt, let's say we first calibrated the force sensor and then did the exact same experiment but...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
The Crazy Eddie fraud may appear smaller and gentler than the massive billion-dollar frauds exposed in recent times, such as Bernie Madoffs Ponzi scheme, frauds in the subprime mortgage market, the...
-
In what significant way do financial audits in government and not-for-profit organizations differ from those carried on in businesses? Your answer should also address the purpose of performance...
-
Water flows rapidly in a channel 25 cm deep. Piercing the surface with a pencil point creates a wedge-like wave of included angle 38°, as shown. Estimate the velocity V of the water flow. 1.1
-
Let denote the L2 inner product on the interval [0, ]. Suppose u(x) satisfies u(0) = u(t) = 0. Write out the integration by parts formula (δy, u) = (Ïy, u) = - (Ïy,u'). and...
-
Figure 20.28 shows the flowsheet for the HDA process for the production of benzene from toluene and hydrogen. Feed streams of toluene (entering through valve V-1) and hydrogen (entering through valve...
-
Ken Iwig is the president, founder, and majority owner of Olathe Medical Corporation, an emerging medical technology products company. Olathe is in dire need of additional capital to keep operating...
-
What do you think is important to employees to make them stay and continue with a company through all the growth and changes that can happen?
-
Darcelle teaches skiing at the Snow School. Jack runs a ski shop called Up Hill, Down Hill. Their combined bank interest is $1,000. If they get a refund, they would like the money deposited into...
-
Mattis wants to become stronger, so he decides to set up a self-modification program that relies on basic principles of learning. Based on his psychology knowledge, he knows that the first thing he...
-
Oslo Company prepared the following contribution format income statement based on a sales volume of 1,000 units (the relevant range of production is 500 units to 1,500 units): Sales Variable expenses...
-
Sam has a 1 1 year annuity due making annual payments that grow geometrically at the rate of 3 . 2 5 0 % . The first payment is $ 8 , 5 0 0 . 0 0 and the interest rate is i ( 1 2 ) = 2 . 0 0 0 % $ ....
-
1. Describe, to the best of your ability, what constitutes a moment/experience of "identity-consciousness" (or, more strongly, identity crisis), as identified by Erik Erikson and discussed by Beverly...
-
Equivalent Units of Conversion Costs The Rolling Department of Kraus Steel Company had 1,806 tons in beginning work in process inventory (60% complete) on October 1. During October, 30,100 tons were...
-
Jordan Corporation began operations on March 1, 2023. The following data is available for March 31: Accounts payable Accounts receivable Cash Contributed capital Expenses, excluding income taxes...
-
A female presents with 8 weeks amenorrhea with pain left lower abdomen. On USG, there was thick endometrium with mass in lateral adnexa. Most probable diagnosis a) Ectopic pregnancy b) Torsion of...
-
What is the purpose of the journal wizard?
-
Write the answers to the following questions in the space provided. Note: all the following questions relate to March 814. Register Reports 1. What is the total amount of purchase orders for the...
-
1. What is the total amount of purchase orders shown on the purchases orders register for the month of March? 2. What is the total of the Invoice Amount column shown on the Purchase Invoice Register...
-
If the statement is true, write a T in the space provided. If the statement is false, write F in the space provided. Only transactions that do not involve cash may be recorded in the General Journal...
-
A company with a price-earnings ratio of 14 has an earnings yield closest to: A. 7.14% B. 14.00% C. 92.86%
-
Which of the following is not produced by a company to communicate its financial results? A. Schedule 13D. B. Annual report. C. 10-K statement.
-
The role of the 8-K filing with the Securities and Exchange Commission is best described as to disclose: A. the annual report. B. executive compensation. C. holdings of beneficial owners.
Study smarter with the SolutionInn App