Deepak Mishra Blogs

Car Loan EMI Analysis Most of the youngster dream to drive their own car after getting a good job, they don’t worried about the cost of car just set go to the showrooms to get it done, but when the finance Company explained them regarding the EMI Schemes then they feel little bit confused regarding the cost and total payment of car after loan. Now I have developed a solution to understand the calculation of Down-Payment, Amount of Loan, EMI and Total Cost of the Car.  My program will analyze your budget Car, you just required to put few inputs Cost of the Car Loan Percentage (After Reducing Down-Payment Tenure of Loan Interest Rate Applied by finance company(assumed to fixed throughout) Just on a single Click you can calculate Down-Payment, Monthly EMI, Cost of Car after Loan, it will show results to you. You can change the input variable and click calculates to get more effective results. I have taken an Example of buy Honda Amaze and Swift Desire on EMI (Re...

VBA- Add Date Picker in Userforms Option 1 | Check if ability already exists | 2 minutes     Open VB Editor     Tools -> Additional Controls     Select "Microsoft Monthview Control 6.0 (SP6)" (if applicable)     Use 'DatePicker' control for VBA Userform Option 2 | The "Monthview" Control doesn't currently exist | 5 minutes     Close Excel     Download MSCOMCT2.cab (it's a cabinet file which extracts into two useful files)     Extract Both Files | the .inf file and the .ocx file     Install | right-click the .inf file | hit "Install"     Move .ocx file | Move from "C:\Windows\system32" to "C:\Windows\sysWOW64"     Run CMD | Start Menu -> Search -> "CMD.exe" | right-click the icon | Select "Run as administrator"     Register Active-X File | Type "regsvr32 c:\windows\sysWOW64\MSCOMCT2.ocx"     Open Excel | Open VB Editor     Activa...

VBA - Additional Controls Dialog Box "MISSING" Additional Controls Dialog Box "MISSING" solution, let's do this. 1-click on start menu( in the left on your desk top ) 2-drage a shortcut of Microsoft Excel in your desktop 3-Right click on it and choose Properties then click on compatibility Tab 4-set check on run this program in compatibility mode for 5-Apply then OK Now open Microsoft Excel or (any ms Application like Word, ppt etc), You can see everything is OK and additional Control box is working well This problem usually happen when we change or config our Microsoft to new version( 2003 to 2010 ,.....)

TDS Calculation System Calculation of how much TDS should deduct from salary will become headache for every employee who will come in the criteria of Tax. In the month of Feb and March employee loosing patient and spent most of the time in internet and communicate with other employee and accountant for better solution to save tax but they do not get appropriate solution. I have designed a nice TDS Calculation System to calculator which will give right and valued information from which the get the scenario of interest and monthly TDS. Download File

Tele-calling Performance Dashboard for xyz company We need to find out the following things in it. Top Customers by Sales Employee Performance by Sales Monthly Sales As well as Calls Call Duration Analysis Download File

Question 1 . Create a macro that generates the output file Master Log using three input files Submission , Endorsement & Bound . Solution of Question 1 Question 2. Create a form that gives the user the ability to filter on the Status field of the Master Log. Solution of Question 2 (in Process)

Sales Report of XYZ Comapny For every company or institution which is build or started for profit generation, the sales are very important because that is the main backbone of every organization which keep its momentum well equipped for the future. In every company or organization, the top leadership generally focused on its sales, performance of staff and for this process, heavy amount is generated to keep the sales upright and forward in comparison to other organization because of this, they can survive in the market. To this preview, we have analyzed the sales of XYZ Company generating the fake data for any knowledgeable person who can easily understand by looking at the graph generated by us. We have deeply focused on the employee’s sales because without them, the sales of a company cannot grow. For example, if we analyze the overall performance of Puneet, he has sold a total of 2806 units out of which in Delhi, his performance is not up to the mark because he...

Loan EMI Calculator Dashboard Generally the Loan EMI calculation is a headache for every customer when  demanding a sum of money from any bank. The customers search in the internet for better solution but they do not get appropriate solution. We have designed a strong EMI loan calculator which will give right and valued information from which the customers get the scenario of interest and monthly EMI. For example, if any customer is taking 10 Lakh loan at the rate of 10% for the period of 15 years, he/she has to pay Rs 10,746 every month to the bank and if a customer is taking 5 Lakh loan at the rate of 10%, he/she has to pay Rs 5373 every month to the respective institution from where he/she has taken the loan. For Rs 7,50000, the customer has to pay Rs 8059 to the bank at the rate of 10% for the period of 15 years. For the loan of 10 Lakh, the customer has to pay 9,34289 as interest which will build up to 19,34,289 as total in 15 years whereas for the ...

'Macro Program to find Lowest Value and Bold in Range A:F Sub Macroto_Bold_MinValue()     For J = 1 To Application.WorksheetFunction.CountA(Range("a:a"))         x = Application.WorksheetFunction.Min(Range("A" & J & ":f" & J))             For i = 1 To 6                 If (Cells(J, i) = x) Then                     Cells(J, i).Font.Bold = True                 Else                     Cells(J, i).Font.Bold = False                 End If             Next i     Next J End Sub

VBA Code to Select All used Rows and Column in a sheet? 'ThisWorkbook.Sheets("Sheet1").UsedRange.Select 'ThisWorkbook.Sheets("Sheet1").Range("a1").CurrentRegion.Select VBA Code to Select Last Used Cell of Row? Selection.End(xlDown).Select Range("A100000").End(xlUp).Select VBA Code to Select Last Cell of Row? Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ‘or Range(Range("A100000").end (xlup)).select Range(Selection, Selection.End(xlup)).Select VBA Code to Select Last Used Cell of Column? Selection.End(xltoRight).Select Range("XFD1").End(xltoleft).Select VBA Code to Select Last Cell of Column? Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range("XFD1").End(xlToLeft).Select Range(Selection, Range("a1")).Select

Sub NonBlankCells() 'Macro Code to Select All Non Blank Cells     On Error Resume Next     Union(Cells.SpecialCells(xlCellTypeFormulas, 23), Cells.SpecialCells(xlCellTypeConstants, 23)).Select     If Err.Number <> 0 Then         Cells.SpecialCells(xlCellTypeFormulas, 23).Select     Else         Exit Sub     End If     If Err.Number <> 0 Then         Cells.SpecialCells(xlCellTypeConstants, 23).Select     Else         Exit Sub     End If     On Error GoTo 0 End Sub

Sub practice1_loops15feb() 'Code to add a folder name months MkDir "D:\Study\Excel and VBA Practice\Training Clases\months" 'Code to add 12 workbooks by month names For x = 1 To 12 name1 = MonthName(x, True) Workbooks.Add ActiveWorkbook.SaveAs "D:\Study\Excel and VBA Practice\Training Clases\months\" & name1 & ".xlsx" ActiveWorkbook.Close True Next x End Sub Sub practice2_loops15feb() 'Code to make a list of month in Sheets1 For x = 1 To 12 name1 = MonthName(x, True) Sheets("Sheet2").Cells(x, 1) = name1 Next x End Sub Sub practice3_loops15feb() 'Code to add Sheets For i = 1 To 9 Sheets.Add after:=Sheets(Sheets.Count) Next i '1st Method to rename Sheets For x = 1 To 12 Sheets("Sheet" & x).Name = Sheets("Sheet2").Cells(x, 1) Next x '2nd Method to rename Sheets For x = 1 To 12 name1 = MonthName(x, True) Sheets("Sheet" & x).Name = name1 Next x End Sub Sub practice4_loops15feb() ...

'Display the value of the variable in a dialog box Sub variables()    'Declaring the variable    Dim my_variable As Integer    'Assigning a value to the variable    my_variable = 12    'Displaying the value of my_variable in a MsgBox    MsgBox my_variable 'Example : whole number Dim nbInteger As Integer nbInteger = 12345     'Example : decimal number Dim nbComma As Single nbComma = 123.45 'Example : text Dim varText As String varText = "Excel-Pratique.com" 'Example : date Dim varDate As Date varDate = "06.04.2012" 'Example : True/False Dim varBoolean As Boolean varBoolean = True     'Example : object (Worksheet object for this example) Dim varSheet As Worksheet Set varSheet = Sheets("Sheet2") 'Set => assigning a value to an object variable 'Example of how to use object variables : activating the sheet varSheet.Activate Dim last_name As String, first_name As String, age As Integer End Sub

'Macro program for Sumif functions or Array Sub Macro2Sumif()     Range("B1").Select     Range(Selection, Selection.End(xlDown)).Select     Selection.Copy     Range("S1").Select     ActiveSheet.Paste     Application.CutCopyMode = False     Range("S1").Select     Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo     Range("T1") = "English"     Range("U1") = "Hindi"     Range("V1") = "Math"     Range("W1") = "Science"     Range("X1") = "Computer"     For R = 2 To 4 For C = 20 To 24 Cells(R, C) = Application.WorksheetFunction.SumIf(Range("$B$2:$G$201"), Cells(R, 10), Range("c$2:c$201")) / Application.WorksheetFunction.Sum(Range("c$2:c$201")) Next C Next R End Sub

Sub properties() 'Macro Program to VBA Properties 'Range("A8").Activate 'Range("A8") = 48 'Range("A8").Value = 48 'Range("A8").Value = "Sample Text" 'Sheets("Sheet1").Range("A8").Value = "Sample text" 'Sheets(1).Range("A8").Value = "Sample text" 'Workbooks("Properties.xlsm").Sheets("Sheet1").Range("A8").Value = "Sample Text" 'Range("A2:A4").ClearContents 'Range("A5:A10").Font.Size = 18 'Range("A5:A10").Font.Bold = True 'Range("A5:A10").Font.Bold = False 'Range("A5:A10").Font.Italic = True 'Range("A5:A10").Font.Underline = True 'Range("A5:A10").Font.Name = "Arial" 'Range("A5:A10").Borders.Value = 1 'Range("A5:A10").Borders.Value = 0 'Selection.Borders.Value = 1 'Sheets("...

'Macro  Program for Dual For Loop Sub Macro2PrintList() 'VBA Code to Print AC, PB List Dim AC As Integer Dim I As Integer Dim K As Integer K = 2 For I = 2 To 13 For AC = 1 To Cells(I, 2).Value Cells(K, 5) = Cells(I, 1) Cells(K, 6) = AC K = K + 1 Next AC Next I End Sub

'VBA Macro Program to make Raw Data for testing Sub Macro2samples_Rawdata() Sheets("Raw Samples").Activate Dim A As Integer Dim I As Integer Dim K As Integer K = 2 For I = 2 To 33 For A = 1 To Sheets("Survey Area").Cells(I, 3).Value Sheets("Raw Samples").Cells(K, 2) = Sheets("Survey Area").Cells(I, 2) K = K + 1 Next A Next I Dim S As Double For S = 1 To 1280 Cells((S + 1), 1) = S Next S Cells(2, 7).Select ' Age (18-60) ActiveCell.Formula = "=RANDBETWEEN(18,60)" Cells(2, 9).Select ' Sex (1,2) ActiveCell.Formula = "=RANDBETWEEN(1,2)" Cells(2, 10).Select ' Q 1 (1,12) ActiveCell.Formula = "=RANDBETWEEN(1,12)" Cells(2, 11).Select ' Q 2 (1,3) ActiveCell.Formula = "=RANDBETWEEN(1,3)" Cells(2, 12).Select ' Q 3 (1,3) ActiveCell.Formula = "=RANDBETWEEN(1,3)" Cells(2, 13).Select ' Q 4 (1,6) ActiveCell.Formula = "=RANDBETWEEN(1,6)" Cells(2, 14).Select ' Q 5 (1,6) A...

' VBA Macro to Filter Sub Macro2Filter() ' ' Program to filter in Column f, select 1, 2 and 3 and then delete it ' '     Selection.AutoFilter     Sheets("Sheet1").Range("F1").Select     ActiveSheet.Range("$A$1:$F$954").AutoFilter Field:=6, Criteria1:=Array("1", _         "2", "3"), Operator:=xlFilterValues     Range("F2").Select     Range(Selection, Selection.End(xlDown)).Select     Selection.Delete Shift:=xlUp     Selection.AutoFilter     End Sub

'Macro Program to Download Election Result 2015 Sub ERMacro_of_Dwonload_Result() Cells(1, 1) = "AC No" Cells(1, 2) = "AC_Name" Cells(1, 3) = "Result" Cells(1, 4) = "Candidate" Cells(1, 5) = "Party" Cells(1, 6) = "Votes" Cells(1, 7) = "Position" ActiveSheet.Cells(2, 7).Formula = "=COUNTIF(B2:$B$2,B2)" Rng1 = 2     For i = 1 To 2   Lnk = "URL;http://eciresults.nic.in/ConstituencywiseU05" & i & ".htm?ac=" & i n = "ConstituencywiseU05" & i & ".htm?ac=" & i Cells(Rng1, 1) = i         With ActiveSheet.QueryTables.Add(Connection:= _         Lnk, Destination:= _         Cells(Rng1, 4))         .Name = n         .FieldNames = True         .RowNumbers = False         .FillAdjacentFormulas = False...

' Macro Program to Countif Function in Column B and result in F Sub ProgramCountif() '     Sheets("Sheet1").Cells(2, 6).Select     ActiveCell.Formula = "=COUNTIF(B2:$B$2,B2)"     Range("B2").Select     Selection.End(xlDown).Select     Lastcell = ActiveCell.Row     Rng = "F" & Lastcell     Range(Rng).Select     Range(Selection, Selection.End(xlUp)).Select     Selection.FillDown     Selection.End(xlUp).Select End Sub

'Macro to Check VBA Colors Sub Macro2Color() Range("A1").Font.Color = vbRed Range("B1").Font.Color = vbGreen Range("C1").Font.Color = vbYellow Range("D1").Font.Color = vbBrown Range("E1").Font.Color = vbPink Range("f1").Font.Color = vbBlue Range("G1").Font.Color = vbGrey For i = 1 To 56 With Sheets("Color_Index") .Cells((i + 1), 1).Font.ColorIndex = i .Cells((i + 1), 2).Borders.ColorIndex = i .Cells((i + 1), 3).Interior.ColorIndex = i End With Next i For i = 3 To 5 Sheets(i - 2).Tab.ColorIndex = i Next i End Sub  

'Macro to Create Form in Excel Private Sub Cmdbutton_add_Click() Dim iRow As Long 'find Next empty row in database iRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1 'check for a Name number If Trim(Me.textbox_name.Value) = "" Then Me.textbox_name.SetFocus MsgBox "Please complete the form" Exit Sub End If 'copy the data to the database Sheets("Sheet1").Cells(iRow, 1) = Me.textbox_name Sheets("Sheet1").Cells(iRow, 2) = Me.textbox_surname Sheets("Sheet1").Cells(iRow, 3) = Me.textbox_age Sheets("Sheet1").Cells(iRow, 4) = Me.textbox_gender MsgBox "Data added", vbOKOnly + vbInformation, "Data Added" 'clear the data Me.textbox_name.Value = "" Me.textbox_surname.Value = "" Me.textbox_age.Value = "" Me.textbox_gender.Value = "" Me.textbox_name.SetFocus End Sub Private Sub Cmdbutton_close_Click() Unload Me End Sub Private Sub surname_...

'Macro to Get 3 Different Workbook data in Master Log File Sub Macro_VBA_Test() 'Open Workbook Submission Workbooks.Open "D:\Study\Excel and VBA Practice\VBA Codes\VBA Test\Submission.xlsx" Workbooks("Master Log.xlsx").Sheets("Sheet1").Activate Cells(2, 1).Select Selection.End(xlDown).Select Dim Lastcell As Integer Lastcell = ActiveCell.Row + 1 Dim x As Integer 'Count total Rows in workbook Submission x = Application.WorksheetFunction.CountA(Workbooks("Submission.xlsx").Sheets("Sheet1").Range("A:A")) For i = 2 To x 'Copy Submission Data into Master Log With Workbooks("Master Log.xlsx").Sheets("Sheet1") .Cells(Lastcell, 1) = Workbooks("Submission.xlsx").Sheets("Sheet1").Cells(i, 1) .Cells(Lastcell, 2) = Workbooks("Submission.xlsx").Sheets("Sheet1").Cells(i, 2) .Cells(Lastcell, 3) = Workbooks("Submission.xlsx").Sheets("Sheet1").Cell...

'VBA Macro to  get Last cell Sub Lastcells1() Sheets("Sheet1").Range("a1").Select Selection.End(xlDown).Select x = ActiveCell.Row + 1 MsgBox x Sheets("Sheet1").Range("a1").Select Selection.End(xlToRight).Select y = ActiveCell.Column + 1 MsgBox y End Sub

Sub DelhiER2015Downloadcode()      ' VBA Macro Code to download Delhi Assembly Election Result 2015 'Sheets.Add before:=Sheet1 Sheets("Sheet1").Activate ActiveSheet.Name = "List" Range("A1") = "AC No" Range("B1") = "Link" Range("C1") = "Name" Dim k As Integer For k = 1 To 70 Cells((k + 1), 1) = "http://eciresults.nic.in/ConstituencywiseU05" & k & ".htm?ac=" & k Cells((k + 1), 2) = k Cells((k + 1), 3) = "ConstituencywiseU05" & k & ".htm?ac=" & k Next k ' Code to add a Sheet name ER where we download election result 'Sheets.Add before:=Sheet1 Sheets("Sheet3").Activate ActiveSheet.Name = "Winning Candidate"        Cells(1, 1) = "AC No"     Cells(1, 2) = "AC Name"     Cells(1, 3) = "Win Candidate"     Cells(1, 4) = "Win Party"     Cells(1, 5)...