Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
Public Sub sendmultipleemail()
Dim sh As Worksheet
Set sh = ThisWorkbook.sheets("Send_Mails")
Dim i As Integer
Dim OA As object
Dim msg As object
Set OA = createObject("outlook.application")
Dim last_row As Integer
last_row = Application.countA(sh.Range("A:A"))
For i =2 To last_row
Set msg = OA.createItem(0)
msg.To = sh.Range("A & i).value
msg.subject= sh.Range("B" & i).value
msg.body = sh.Range("C" & i).value
msg.Display
Next i
End Sub
---------------------------------------------------------------------------------------------------------------------
special worksheet required for
check spelling mistakes in the workbook using VBA code Sub
Highlight _Misspelled_Words()Dim cell As
Range for Each cell in active sheet.usedRangeIf
Not Application.checkSpelling(Word:=cell.Text)
Then cell.Interior.colorindex=6
Next
End Sub
Tried to run this code but my excel 2016 is not responding because its a huge data so hence writing the code here
--------------------------------------------------------------------------------------------------------------------
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:=xlGreator,_
Formul:="=540000"
selection.FormatConditions(Selection.Form
atconditions.Count).SetFirstPriority
With selection.FormatConditions(1).Font
.color=-16754788
.TintAndShade=0
End with
with selection.FormatConditions(1).interior
patternColourindex = xlAutomatic
.color = 10284031
.TintAndShade = 0
End with
With selection. FormatConditions(1).StopifTrue =
False
selection.FormatConditions.Add
Type:=xlcellValue,Operator:=xlLess,_
Formula1:="=220000"
selection.Formatconditions(1).Font
.color = -16752384
.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 form applicant data
Sub PrintSelectionToPDF()
Dim ThisRng As range
Dim strfile As string
Dim myfile As Varient
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.ExpertAsFixedFormat
Type:=xlTypePDF,Filename:= _
myfile,Quality:=xlQualityStandard,
inxludeDocProperties:=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
wdinterior.color = vbYellow
End if
Next wd
End Sub
------------------------------------------------------------------------------------------------------------------
data summary for all flags columns like(FLAG_DOCUMENT_2,FLAG_DOCUMENT_3etc.)using all variants of DCOUNT function
FLAG
FLAG_DOCUMENT_2 to FLAG_DOCUMENT_21
DCOUNT for 1 COUNTA Function COUNTBLANK Function
data summary of all female Client's for Income and Loan Amount using New Functions
Functions INCOME LOAN