Posts

Showing posts from February, 2015

Last Cell Selection Methods

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
Post a Comment
Read more

Macro Code to Select All Non Blank Cells

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
Post a Comment
Read more

Practice of for Loops

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() ...
Post a Comment
Read more

VBA Variable Declarations

'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
Post a Comment
Read more

'Macro program for Sumif functions or Array

'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
Post a Comment
Read more

'Macro Program to VBA Properties

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("...
Post a Comment
Read more

Macro Program for Dual For Loop

'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
Post a Comment
Read more

VBA Macro Program to make Raw Data for testing

'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...
Post a Comment
Read more

VBA Macro to Filter

' 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
Post a Comment
Read more

Download Election Result 2015

'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...
Post a Comment
Read more

Countif Function

' 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
Post a Comment
Read more

VBA Colors

'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  
Post a Comment
Read more

Create Form in Excel

'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_...
Post a Comment
Read more

Get 3 Different Workbook data in Master Log File

'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...
Post a Comment
Read more

VBA Macro to get Last 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
Post a Comment
Read more

Download Delhi ER2015

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)...
Post a Comment
Read more