Lock your system using Rundll32.exe

Sub lock_system()
Dim cm As String
cm = "Rundll32.exe User32.dll,LockWorkStation"
Shell cm
End Sub

VBA Page Down

ActiveSheet.Previous.Select

VBA Page UP

ActiveSheet.Next.Select

NIIT has given this opportunity for all those who believe "Still lot more to Learn".

NIIT has given this opportunity for all those who believe "Still lot more to Learn".

Come Join and learn, get your expertise, get skilled and groom yourself with NIIT.

http://www.niit.tv/NIITOnlineLearning/

 - "I teach to learn" Learning never ends.

Count occurrence > 1 (Duplicates Check)

=COUNTIF(A:A,A:A)>1

Excel Counting between two numbers using Countifs

=COUNTIFS(A13:A17,">=" & 2,A13:A17,"<=" & 5)
 

Excel using Rept keyword with space

=REPT(A2 & " ",3)

A2 contains some text
& operator used to add space with every repetition of text
3 is the count to repeat text 

Sumif based on name, start and end date

By this example you can pull the data in excel based on name, start and end date

 

 

=SUMIFS($C$2:$C$32,$B$2:$B$32,I4,$A$2:$A$32,">="& $G$2,$A$2:$A$32,"<=" & $H$2)

 

to download the example click on the below link 

 

http://s000.tinyupload.com/?file_id=24613670605322543211

 

 

 





Using excel data cleaning functions CLEAN () TRIM () & PROPER()

Scenario - cell A1 contains text in   Camel case "EXamPlE TEXT " with some extra spaces and non printable characters

Use  combination of data cleaning functions in cell A2   =CLEAN(TRIM(PROPER(A1)))

You will get desired output as "Example Text".

Please note specially in case of Postal code/Zip Code use UPPER function by replacing PROPER as generally they are required in upper case.    

  

Excel using IF condition with RIGHT function to get two decimal values if cell value >= 8

=IF(B2>=8,RIGHT(B2,2))

Example we have to fetch two decimal values if cell value is >= 8

By assuming B2 contains 8.32 , the output would be 32        

Simple split VBA function with comma seprated values

Sub Split_2()

Dim name As String
Dim Split_name() As String ' Array String

name = InputBox("Enter your name using comma between first & last name: ")

Split_name = Split(name,",") ' Split function used - comma seprated

Range("a1").Value = Split_name(0) 'stored value before space
Range("b1").Value = Split_name(1) ' stored value after space

End Sub

VBA split function example

Sub Split_1()

Dim name As String
Dim Split_name() As String ' Array String

name = InputBox("Enter your name: ")

Split_name = Split(name) ' Split function used - by default this is with space
   
Range("a1").Value = Split_name(0)  'stored value before space
Range("b1").Value = Split_name(1) ' stored value after space

End Sub

 

Macro to check if cell contains specific value and color entire row as red

Dim cell1 As Range

 Dim rng1 As Range

 Set rng1 = ActiveSheet.UsedRange

For Each cell1 In rng1

 On Error Resume Next

If cell1.Value Like "*.pdf*"  then

cell1.EntireRow.Interior.Color = vbRed

 End If

 Next cell1

VBA to download and save excel file from website, also copy the contents to new file

Sub Download_File()

Windows("Example.xlsm").Activate ' activate your specific macro file
Cells.ClearContents

Dim file As String
Dim path As String
path = "Path where you want to save the file"  ' Example:-  C:\Test\
file = "Path of the file that you want to download" ' Example : - www.example.com/one.csv

ThisWorkbook.FollowHyperlink file ' parsing file URL
With ActiveWorkbook
.SaveAs path & "new.csv"  ' altering file name and type to the location

.Save
.ActiveSheet.Range("A:C").Copy  ' Copying the data of column A - C from the downloaded file
Application.DisplayAlerts = False ' Alerts disabled to ensure file is closed without any prompt message
.Close
Application.DisplayAlerts = True

End With

Windows("Example.xlsm"). ' re activate your specific macro file
ActiveSheet.Range("a1").Select  ' select and paste the copied data
Range("a1").PasteSpecial xlPasteAll
Range("a1").Select

End Sub

Calculate Months between two dates (Calculate employee tenure in months)

=DATEDIF(G2,TODAY(),"m")

* G2 is the start date (Example Employee joining date)