Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
PS: NOTE THAT EXCEL IS CRASHING WHILE RUNNING THE VBA EDITOR SO THAT I COPIED THE CODE TO THE TEXT BOX AND ALSO I CANT ALSO ANSWER THE DATA SUMMARY QUESTION ALSO DUE SLOW PROCESS TIME AND CRASHING KINDY SEE TO IT. THANK YOU
GOOGLE DRIVE LINK -
https://drive.google.com/drive/folders/1DZNBI3mpDS40eNMlANW7okXA7hAl2hwS?usp=sharing
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.
31588428942 or Count - 202448
Data Summary of All male Client’s for Income and Loan Amount using list Functions (hint:use dsum,daverage,..)
- 20318041019 or Count - 105059
Data Summary for all Flag Columns like (FLAG_DOCUMENT_2, FLAG_DOCUMENT_3 etc.) using all variants of DCOUNT function.
| Count of FLAG_DOCUMENT_2 |
| 105059 |
| Count of FLAG_DOCUMENT_3 |
| 105059 |
Create VBA Code to convert selected data into pdf from applicant data
Sub ConvertToPDF()
' Select the range you want to convert to PDF
Dim selectedRange As Range
On Error Resume Next
Set selectedRange = Application.InputBox("Select the data range", Type:=8)
On Error GoTo 0
If Not selectedRange Is Nothing Then
' Create a new sheet and copy the selected range to it
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "TempSheet"
selectedRange.Copy Destination:=Sheets("TempSheet").Range("A1")
' Save the new sheet as PDF
Sheets("TempSheet").ExportAsFixedFormat Type:=xlTypePDF, Filename:="SelectedData.pdf"
' Delete the temporary sheet
Application.DisplayAlerts = False
Sheets("TempSheet").Delete
Application.DisplayAlerts = True
MsgBox "PDF file created successfully!", vbInformation
Else
MsgBox "Operation canceled or invalid selection.", vbExclamation
End If
End Sub
Check Spelling mistakes in the workbook using VBA Code
Sub CheckSpelling()
Dim ws As Worksheet
Dim cell As Range
On Error Resume Next ' Ignore errors temporarily
' Loop through all worksheets in the workbook
For Each ws In ThisWorkbook.Sheets
' Loop through all cells in the worksheet
For Each cell In ws.UsedRange
' Check spelling in each cell
If Not IsEmpty(cell.Value) Then
If Not Application.CheckSpelling(cell.Value) Then
' Highlight cells with spelling mistakes
cell.Interior.Color = RGB(255, 0, 0)
End If
End If
Next cell
Next ws
On Error GoTo 0 ' Reset error handling to default
MsgBox "Spelling check complete.", vbInformation
End Sub