VBA Code check Given number is wheather Prime or Not

Dim divisors As Integer, number As Long, i As Longdivisors = 0number = InputBox("Enter a number")For i = 1 To numberIf number Mod i = 0 Thendivisors = divisors + 1End IfNext iIf divisors = 2 ThenMsgBox number & " is a prime number"ElseMsgBox number & " is not a prime number"End If

VBA Code to Find Second Highest

Dim rng As Range, cell As RangeDim highestValue As Double, secondHighestValue As DoubleSet rng = SelectionhighestValue = 0secondHighestValue = 0For Each cell In rngNext cellIf cell.Value > highestValue Then highestValue = cell.ValueFor Each cell In rngNext cellIf cell.Value > secondHighestValue And cell.Value < highestValue Then secondHighestValue = cell.ValueMsgBox "Second Highest Value is " & secondHighestValue

VBA Code to Sum By ColorDim toReceive As Integer, i As IntegertoReceive = 0For i = 1 To 12If Cells(i, 1).Font.Color = vbRed Then toReceive = toReceive + Cells(i, 1).ValueEnd IfNext iMsgBox "Still to receive " & toReceive & " dollars"

VBA Code to Deleter Blank CellsDim counter As Integer, i As Integercounter = 0For i = 1 To 10 If Cells(i, 1).Value "" Then Cells(counter + 1, 2).Value = Cells(i, 1).Value counter = counter + 1 End IfNext i

VBA Code for operating on Range of Values (Dynamic Range)Dim rng As Range, cell As RangeSet rng = Range("A1:A3")For Each cell In rngcell.Value = cell.Value * cell.ValueNext cellSet rng = Range("A1:A3")Set rng = SelectionVBA CODE to Loop through Entire ColumnDim i As LongColumns(1).Font.Color = vbBlackFor i = 1 To Rows.CountIf Cells(i, 1).Value < Range("D2").Value And Not IsEmpty(Cells(i, 1).Value) Then Cells(i, 1).Font.Color = vbRedEnd IfNext iDo Until LoopDim i As Integeri = 1Do Until i > 6 Cells(i, 1).Value = 20 i = i + 1Loop

Sort NumbersDim i As Integer, j As Integer, temp As Integer, rng As RangeSet rng = Range("A1").CurrentRegionFor i = 1 To rng.CountFor j = i + 1 To rng.CountIf rng.Cells(j) < rng.Cells(i) ThenEnd Iftemp = rng.Cells(i)rng.Cells(i) = rng.Cells(j)rng.Cells(j) = tempNext jNext iRandomly Sort DataFor i = 1 To 5 Cells(i, 2).Value = WorksheetFunction.RandBetween(0, 1000)Next iFor i = 1 To 5 For j = i + 1 To 5If Cells(j, 2).Value < Cells(i, 2).Value ThentempString = Cells(i, 1).ValueCells(i, 1).Value = Cells(j, 1).ValueCells(j, 1).Value = tempStringtempInteger = Cells(i, 2).ValueCells(i, 2).Value = Cells(j, 2).ValueCells(j, 2).Value = tempIntegerEnd If Next jNext i

Remove Duplicates Dim toAdd As Boolean, uniqueNumbers As Integer, i As Integer, j As IntegerCells(1, 2).Value = Cells(1, 1).ValueuniqueNumbers = 1toAdd = TrueFor i = 2 To 10For j = 1 To uniqueNumbers If Cells(i, 1).Value = Cells(j, 2).Value Then toAdd = False End IfNext jIf toAdd = True Then Cells(uniqueNumbers + 1, 2).Value = Cells(i, 1).Value uniqueNumbers = uniqueNumbers + 1End IftoAdd = TrueNext i

Separate StringsDim fullname As String, commaposition As Integer, i As IntegerFor i = 2 To 7 fullname = Cells(i, 1).Value commaposition = InStr(fullname, ",")Cells(i, 2).Value = Mid(fullname, commaposition + 2)Cells(i, 3).Value = Left(fullname, commaposition - 1)Next i

Reverse StringsDim text As String, reversedText As String, length As Integer, i As Integertext = InputBox("Enter the text you want to reverse")length = Len(text) For i = 0 To length - 1reversedText = reversedText & Mid(text, (length - i), 1)Next imsgbox reversedTextConvert to Proper CaseDim rng As Range, cell As RangeSet rng = SelectionFor Each cell In rngIf Not cell.HasFormula Thencell.Value = WorksheetFunction.Proper(cell.Value)End IfNext cellReverse StringsDim text As String, reversedText As String, length As Integer, i As Integertext = InputBox("Enter the text you want to reverse")length = Len(text) For i = 0 To length - 1reversedText = reversedText & Mid(text, (length - i), 1)Next imsgbox reversedTextConvert to Proper CaseDim rng As Range, cell As RangeSet rng = SelectionFor Each cell In rngIf Not cell.HasFormula Thencell.Value = WorksheetFunction.Proper(cell.Value)End If Next cellCount WordsDim rng As Range, cell As RangeDim cellWords, totalWords As Integer, content As StringSet rng = SelectioncellWords = 0totalWords = 0For Each cell In rngIf Not cell.HasFormula Thencontent = cell.Valuecontent = Trim(content)

End If

Next cellIf content = "" Then cellWords = 0Else cellWords = 1End IfDo While InStr(content, " ") > 0content = Mid(content, InStr(content, " "))

Loopcontent = Trim(content)cellWords = cellWords + 1totalWords = totalWords + cellWordsMsgBox totalWords & " words found in the selected range."

The DateDiff function in Excel VBA can be used to get the number of days between two dates.Place a command button on your worksheet and add the following code lines:Dim firstDate As Date, secondDate As Date, n As Integer

firstDate = DateValue("Jun 19, 2010")secondDate = DateValue("Jul 25, 2010")

n = DateDiff("d", firstDate, secondDate)

MsgBox nWeekdaysDim date1 As Date, date2 As Date, dateToCheck As DateDim daysBetween As Integer, weekdays As Integer, i As Integerweekdays = 0date1 = Range("B2")date2 = Range("B3")daysBetween = DateDiff("d", date1, date2)For i = 0 To daysBetweendateToCheck = DateAdd("d", i, date1)If (Weekday(dateToCheck) 1 And Weekday(dateToCheck) 7) Then weekdays = weekdays + 1End IfNext iMsgBox weekdays & " weekdays between these two dates"

Delay a Macro

Sub reminder()Application.OnTime Now() + TimeValue("00:00:05"), "reminder"Application.OnTime TimeValue("14:00:00 am"), "reminder"MsgBox "Don't forget your meeting at 14.30"End SubYear OccurrencesDim yearCount As Integer, yearAsk As Integer, i As IntegeryearCount = 0yearAsk = Range("C4").ValueFor i = 1 To 16If year(Cells(i, 1).Value) = yearAsk Then yearCount = yearCount + 1End IfNext iMsgBox yearCount & " occurrences in year " & yearAsk