Challenge

Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies

MMeenakshi Aggarwal14 Aug 2023

Separate Worksheet Required for

Data Summary of All Female Client’s for Income and Loan Amount using New Functions
Data Summary of All Male Client’s for Income and Loan Amount using List Function

Excel Sheet link : Separate worksheet for Required answers

Data Summary for all Flag Columns like (FLAG_DOCUMENT_2, FLAG_DOCUMENT_3 etc.) using all variants of DCOUNT function.

Excel Sheet link : Separate Worksheet for Required ansers

Create a Macro for Data Formatting (Conditional Formatting, Freezing Panes) of workbook as same will be applied to the data of next quarter
Sub DataFormatting()
'
' DataFormatting Macro
' Macro for Data Formatting (Conditional Formatting, Freezing Panes)
'
' Keyboard Shortcut: Ctrl+k
'
Range("Applicant_Data[[#All],[AMT_INCOME_TOTAL]]").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=540000"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16754788
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10284031
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=220000"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll Down:=-115
ActiveWindow.ScrollRow = 307484
ActiveWindow.ScrollRow = 307033
ActiveWindow.ScrollRow = 304782
ActiveWindow.ScrollRow = 300731
ActiveWindow.ScrollRow = 251659
ActiveWindow.ScrollRow = 143613
ActiveWindow.ScrollRow = 118852
ActiveWindow.ScrollRow = 90490
ActiveWindow.ScrollRow = 1
Range("E3").Select
ActiveWindow.FreezePanes = True
Range("G9").Select
End Sub
Create VBA Code to convert selected data into pdf from applicant data
Sub PrintSelectionToPDF()

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
Public Sub HighlightSpellingMistakes()

Dim wd As Range

For Each wd In ActiveSheet.UsedRange

If Not Application.CheckSpelling(word:=wd.Text) Then

wd.Font.Color = vbRed

wd.Interior.Color = vbYellow

End If

Next wd

End Sub

Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies

Ship work like this
in your own course.

Talk to a career counsellor

Ship work like thisin your own course.

Talk to a career counsellor

Ship work like this
in your own course.