https://docs.google.com/spreadsheets/d/1eb_1MWvxUVBt1x6mmbJ1fsd_OdRdUeJiWeXKrqWc

Question

https://docs.google.com/spreadsheets/d/1eb_1MWvxUVBt1x6mmbJ1fsd_OdRdUeJiWeXKrqWcs6U/edit?usp=sharing

The Link to .xlxs file you should use is the one above. Your solution file can be kindly shared via a link like google share drive with the steps used. Your solution should consist of two (2) macros.

Exercise 1 (7pt). Record a macro that helps you to prepare inventory reports Use HW1_1.xlsx on Blackboard in Assignment 1. Select tab "dayl" to record the macro. Name it "InventoryReport" and you do not need to specify the shortcut. Record the following steps 1. Select cells A1:B2 (2x2 cells), Merge &Center; the selected cells 2. Type in the merged cell "Inventory Report" 3. Type "IDS 331" in cell B4 4. Type your name in cell B5 5. Enter formula "Today0" in cell B6 6. Format cell B6 as Date, in the type of "mmmm dd, yyyy", for example, "March 14, 2012" Select cell A1, increase the font size to 16, make it Bold, and change it to any color you'd like to Select cells B4:B6, and make the font Bold Enter formula to calculate Ending Inventory in cell B12 (Hint: Ending Inventory = Beginning Inventory - Demand +New Production) 7. 8. 9. Stop recording, save your file. Be careful about your file type Test your recorded macro on tab "test". The resulted worksheet should be the same as you did in "day1". Go to VBE, highlight your recorded macro and copy the code: Insert new module by right click on Modulel and choose Insert Module, paste code in the new module Change the second Sub name into "InventoryReportChangeDate". Modify this new Sub so that the date in cell B6 is the day after today. E.g., if today is 09/01/2017, you should show 09/02/2017 in B6 Save and test your macro "InventoryReportChangeDate" on tab "day2". Submission: Your submission file for exercise 1 should contain 2 macros

Explanation / Answer

Hi,

I have created the solution workbook and placed in your folder. We dont have permission to share links here in the solution. Please try accessing it or use below code.

Below is the code for recorded macro-

Ans 1 (day1 sheet data)

Sub InventoryReport()
'
' InventoryReport Macro
'

'
Range("A1:B2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "Inventory Report"
Range("B4").Select
ActiveCell.FormulaR1C1 = "IDS 331"
Range("B5").Select
ActiveCell.FormulaR1C1 = "Name"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=today"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("B6").Select
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
Selection.NumberFormat = "[$-409]mmmm d, yyyy;@"
Range("A1:B2").Select
With Selection.Font
.Name = "Calibri"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Selection.Font.Bold = True
With Selection.Font
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.249977111117893
End With
Range("B4:B6").Select
Selection.Font.Bold = True
Range("B12").Select
ActiveCell.FormulaR1C1 = "=R[-3]C-R[-2]C+R[-1]C"
Range("B13").Select
End Sub

Ans 2 - (day 2 sheet data)

Sub InventoryReportChangeDate()
'
' InventoryReport Macro
'

'
Range("A1:B2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "Inventory Report"
Range("B4").Select
ActiveCell.FormulaR1C1 = "IDS 331"
Range("B5").Select
ActiveCell.FormulaR1C1 = "Name"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=today"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=TODAY()+1"
Range("B6").Select
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
Selection.NumberFormat = "[$-409]mmmm d, yyyy;@"
Range("A1:B2").Select
With Selection.Font
.Name = "Calibri"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Selection.Font.Bold = True
With Selection.Font
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.249977111117893
End With
Range("B4:B6").Select
Selection.Font.Bold = True
Range("B12").Select
ActiveCell.FormulaR1C1 = "=R[-3]C-R[-2]C+R[-1]C"
Range("B13").Select
End Sub

Thanks and Regards,

Vinay Singh

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.