Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
VBA Code to convert selected data into pdf from applicant data
Sub PrintSelectionToPDF() 'SUBROUTINE: PrintSelectionToPDF 'DEVELOPER: Ryan Wells 'DESCRIPTION: Print your currently selected range to a PDF Dim ThisRng As Range Dim strfile As String Dim myfile As Variant If Selection.Count = 1 Then Set ThisRng = Application.InputBox("Select a range", "Get Range", Type:=8) Else Set ThisRng = Selection End If 'Prompt for save location strfile = "Selection" & "_" _ & Format(Now(), "yyyymmdd_hhmmss") _ & ".pdf" strfile = ThisWorkbook.Path & "\" & strfile myfile = Application.GetSaveAsFilename _ (InitialFileName:=strfile, _ FileFilter:="PDF Files (*.pdf), *.pdf", _ Title:="Select Folder and File Name to Save as PDF") If myfile <> "False" Then 'save as PDF ThisRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=True Else MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected" End If End Sub
- Check Spelling mistakes in the workbook using VBA Code
Sub SpellCheckInSpecificCells()
Range("C7,E9").CheckSpelling
End Sub
- Data Summary of All Female Client’s for Income and Loan Amount using New Functions (hint: use unique,sort,filter functions) if These functions are not supporting in your software please give a theoritical answer.
ANSWER:You can also sort multiple columns and choose which column to sort by. UNIQUE Function – the UNIQUE function can also handle multiple columns and differentiate between unique and distinct values. FILTER Function - you can filter more than one column and FILTER can handle OR criteria as well as AND criteria.
- Data Summary of All male Client’s for Income and Loan Amount using list Functions (hint:use dsum,daverage,..)
What does Daverage mean in Excel?
The DAVERAGE function in Excel is a database function that calculates the average of selected database entries based on specified criteria. This is a very useful function when you need to determine the average of a specific subset of data within a larger dataset.
The Excel DSUM function calculates a sum of values in a set of records that match criteria. The values to sum are extracted from a given field in the database, specified as an argument.