VBA Code check Given number is wheather Prime or Not Dim divisors As Integer, number As Long, i As Long divisors = number = In!utBo"#$%nter a number$& 'or i = ( )o number I* number +od i = )hen divisors = divisors ( %nd I* Ne"t i I* divisors = - )hen +sgBo" number . $ is a !rime number$ %/se +sgBo" number . $ is not a !rime number$ %nd I* VBA Code to 'ind 0econd 1ighest Dim rng As Range, cell As Range Dim highestValue As Double, secondHighestValue As Double Set rng = Selection highestValue = 0 secondHighestValue = 0 For Each cell In rng Next cell I cell!Value " highestValue #hen highestValue = cell!Value For Each cell In rng Next cell I cell!Value " secondHighestValue And cell!Value $ highestValue #hen secondHighestValue = cell!Value %sg&ox 'Second Highest Value is ' ( secondHighestValue
VBA Code to Sum By Color Dim to2eceive As Integer, i As Integer to2eceive =
'or i = ( )o (I* Ce//s#i, (&3'ont3Co/or = vb2ed )hen to2eceive = to2eceive Ce//s#i, (&3Va/ue %nd I* Ne"t i +sgBo" $0ti// to receive $ . to2eceive . $ do//ars$
VBA Code to Deleter Blank Cells Dim counter As Integer, i As Integer counter = 'or i = ( )o ( I* Ce//s#i, (&3Va/ue 45 $$ )hen Ce//s#counter (, -&3Va/ue = Ce//s#i, (&3Va/ue counter = counter ( %nd I* Ne"t i
VBA Code for operating on Range of Values (Dynamic Range) Dim rng As 2ange, ce// As 2ange 0et rng = 2ange#$A(6A7$& 'or %ach ce// In rng ce//3Va/ue = ce//3Va/ue 8 ce//3Va/ue Ne"t ce// 0et rng = 2ange#$A(6A7$& 0et rng = 0e/ection
VBA CODE to Loop troug Entire Column Dim i As Long Co/umns#(&3'ont3Co/or = vbB/ack 'or i = ( )o 2ows3Count I* Ce//s#i, (&3Va/ue 4 2ange#$D-$&3Va/ue And Not Is%m!t9#Ce//s#i, (&3Va/ue& )hen Ce//s#i, (&3'ont3Co/or = vb2ed %nd I* Ne"t i
Do !ntil Loop Dim i As Integer i=( Do :nti/ i 5 ; Ce//s#i, (&3Va/ue = - i=i( Loo!
Sort "um#ers Dim i As Integer, < As Integer, tem! As Integer, rng As 2ange 0et rng = 2ange#$A($&3Current2egion 'or i = ( )o rng3Count 'or < = i ( )o rng3Count I* rng3Ce//s#<& 4 rng3Ce//s#i& )hen %nd I* tem! = rng3Ce//s#i& rng3Ce//s#i& = rng3Ce//s#<& rng3Ce//s#<& = tem! Ne"t < Ne"t i
Randomly Sort Data 'or i = ( )o Ce//s#i, -&3Va/ue = >orksheet'unction32andBetween#, (& Ne"t i 'or i = ( )o 'or < = i ( )o I* Ce//s#<, -&3Va/ue 4 Ce//s#i, -&3Va/ue )hen tem!0tring = Ce//s#i, (&3Va/ue Ce//s#i, (&3Va/ue = Ce//s#<, (&3Va/ue Ce//s#<, (&3Va/ue = tem!0tring tem!Integer = Ce//s#i, -&3Va/ue Ce//s#i, -&3Va/ue = Ce//s#<, -&3Va/ue Ce//s#<, -&3Va/ue = tem!Integer %nd I* Ne"t < Ne"t i
Remo$e Duplicates Dim toAdd As Boo/ean, uni?ueNumbers As Integer, i As Integer, < As Integer Ce//s#(, -&3Va/ue = Ce//s#(, (&3Va/ue uni?ueNumbers = ( toAdd = )rue 'or i = - )o ( 'or < = ( )o uni?ueNumbers I* Ce//s#i, (&3Va/ue = Ce//s#<, -&3Va/ue )hen toAdd = 'a/se %nd I* Ne"t < I* toAdd = )rue )hen Ce//s#uni?ueNumbers (, -&3Va/ue = Ce//s#i, (&3Va/ue uni?ueNumbers = uni?ueNumbers ( %nd I* toAdd = )rue Ne"t i
Separate Strings Dim *u//name As 0tring, comma!osition As Integer, i As Integer 'or i = - )o @ *u//name = Ce//s#i, (&3Va/ue comma!osition = In0tr#*u//name, $,$& Ce//s#i, -&3Va/ue = +id#*u//name, comma!osition -& Ce//s#i, 7&3Va/ue = Le*t#*u//name, comma!osition (& Ne"t i
Re$erse Strings Dim te"t As 0tring, reversed)e"t As 0tring, /ength As Integer, i As Integer te"t = In!utBo"#$%nter the te"t 9ou want to reverse$& /ength = Len#te"t& 'or i = )o /ength ( reversed)e"t = reversed)e"t . +id#te"t, #/ength i&, (& Ne"t i msgbo" reversed)e"t
Con$ert to %roper Case Dim rng As 2ange, ce// As 2ange 0et rng = 0e/ection 'or %ach ce// In rng I* Not ce//31as'ormu/a )hen ce//3Va/ue = >orksheet'unction3Pro!er#ce//3Va/ue& %nd I* Ne"t ce//
Re$erse Strings Dim te"t As 0tring, reversed)e"t As 0tring, /ength As Integer, i As Integer te"t = In!utBo"#$%nter the te"t 9ou want to reverse$& /ength = Len#te"t& 'or i = )o /ength ( reversed)e"t = reversed)e"t . +id#te"t, #/ength i&, (& Ne"t i msgbo" reversed)e"t
Con$ert to %roper Case Dim rng As 2ange, ce// As 2ange 0et rng = 0e/ection 'or %ach ce// In rng I* Not ce//31as'ormu/a )hen ce//3Va/ue = >orksheet'unction3Pro!er#ce//3Va/ue& %nd I* Ne"t ce//
Count &ords Dim rng As 2ange, ce// As 2ange Dim ce//>ords, tota/>ords As Integer, content As 0tring 0et rng = 0e/ection ce//>ords = tota/>ords = 'or %ach ce// In rng I* Not ce//31as'ormu/a )hen content = ce//3Va/ue content = )rim#content&
%nd I*
Ne"t ce// I* content = $$ )hen ce//>ords = %/se ce//>ords = ( %nd I* Do >hi/e In0tr#content, $ $& 5 content = +id#content, In0tr#content, $ $&&
Loo! content = )rim#content& ce//>ords = ce//>ords ( tota/>ords = tota/>ords ce//>ords +sgBo" tota/>ords . $ words *ound in the se/ected range3$
'e DateDi** *unction in %"ce/ VBA can #e used to get te num#er of days #eteen to dates P/ace a command button on 9our worksheet and add the *o//owing code /ines6 Dim )rstDate As Date, secondDate As Date, n As Integer )rstDate = DateValue*'+un -, .00'/ secondDate = DateValue*'+ul ., .00'/ n = DateDi1*'d', )rstDate, secondDate/ %sg&ox n
Weekdays Dim date As Date, date. As Date, date#o2hec3 As Date Dim da4s&et5een As Integer, 5ee3da4s As Integer, i As Integer 5ee3da4s = 0 date = Range*'&.'/ date. = Range*'&6'/ da4s&et5een = DateDi1*'d', date, date./ For i = 0 #o da4s&et5een date#o2hec3 = DateAdd*'d', i, date/ I *7ee3da4*date#o2hec3/ $" And 7ee3da4*date#o2hec3/ $" 8/ #hen 5ee3da4s = 5ee3da4s 9 End I Next i %sg&ox 5ee3da4s ( ' 5ee3da4s bet5een these t5o dates'
Delay a *acro
0ub reminder#& A!!/ication3n)ime Now#& )imeVa/ue#$66$&, $reminder$ A!!/ication3n)ime )imeVa/ue#$(66 am$&, $reminder$ +sgBo" $DonEt *orget 9our meeting at (37$ %nd 0ub
+ear Occurrences Dim 9earCount As Integer, 9earAsk As Integer, i As Integer 9earCount = 9earAsk = 2ange#$C$&3Va/ue 'or i = ( )o (; I* 9ear#Ce//s#i, (&3Va/ue& = 9earAsk )hen 9earCount = 9earCount ( %nd I* Ne"t i +sgBo" 9earCount . $ occurrences in 9ear $ . 9earAsk
