Excel And VBA Tips

Sub ElseIf_Structure() Dim marks As Integer marks = Application.InputBox("Give Marks") If marks >= 33 And marks <= 50 Then MsgBox "Third" ElseIf marks > 50 And marks < 60 Then MsgBox "Secend" ElseIf marks >= 60 Then MsgBox "First" Else MsgBox "Fail" End If End Sub

If  condition  Then statements ElseIf  elseif_condition_1  Then elseif_statements_1 ElseIf  elseif_condition_n  Then elseif_statements_n Else else_statements End If If  statement  ->  In case of a multiple-line syntax (as above), the first line should have only the  If statement. See below for single-line syntax. condition   ->  an expression (could be numeric or string)  which evaluates to True or False (note: a Null  condition  is equated to False). It is necessary to specify a  condition . statements   -> one or more statements (block of code) get executed if the  condition  evaluates to True. If  statements  are not specified, then no code will be executed if the  condition  evaluates to True. ElseIf   ->  this clause can be used (optionally) if you want to test for multiple conditions. It is necessary to specify  elseif_condition  if  ElseIf  is present. Any number of  ElseIf  and  elseif_conditions  can be present. elseif_condition   [elseif_condition_1 … elseif_condi

Sub LastUsedColumn_SpecialCells_1() Dim lastColumn As Integer lastColumn = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column MsgBox lastColumn End Sub Sub LastUsedColumn_SpecialCells_2() Dim lastColumn As Integer lastColumn = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column MsgBox lastColumn End Sub

Sub UsedColumns_UsedRange() Dim usedColumns As Integer usedColumns = ActiveSheet.UsedRange.Columns.Count MsgBox usedColumns End Sub

Sub UsedRows_UsedRange() Dim usedRows As Long usedRows = ActiveSheet.UsedRange.Rows.Count MsgBox usedRows End Sub

Sub LastRowWithData_xlUp_1() Dim lastRow As Long lastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row MsgBox lastRow End Sub Sub LastRowWithData_xlUp_2() Dim lastRow As Long lastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row MsgBox lastRow End Sub

Sub Largest() Dim rng As Range Dim maximum As Double Set rng  Sheet1.Range("A1:Z100") maximum  Application.WorksheetFunction.Max(rng) MsgBox maximum End Sub

Sub Smallest() Dim rng As Range Dim Minimum As Double Set rng = Sheet1.Range("A1:Z100") Minimum = Application.WorksheetFunction.Min(rng) MsgBox Minimum End Sub

Function removeSpecial(sInput As String) As String     Dim sSpecialChars As String     Dim i As Long     sSpecialChars = "\/:*?"" {}[](),!`~\:;'._-=+&^%$<>|"     For i = 1 To Len(sSpecialChars)         sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")     Next     removeSpecial = sInput End Function Sub cleanAllText()    Dim rngUsed As Range, rngCheck As Range    Dim i As Long ' Change Column as per requirement like ("K:K")    Set rngUsed = Range ("J:J")     'Set rngUsed = Range(rngUsed, _       ' rngUsed.SpecialCells(xlLastCell))    Application.Calculation = xlCalculationManual    For Each rngCheck In rngUsed.Cells        If rngCheck.Formula <> "" Then          If Left(rngCheck, 1) <> "=" Then          rngCheck = removeSpecial(rngCheck.Value)          End If        End If    Next rngCheck End Sub

Sub Marks_rank() Dim i As Integer Dim k As String For i = 2 To Sheet1.Range("B1048576").End(xlUp).Row k = Application.WorksheetFunction.Rank(Cells(i, "B"), Range("B:B"), 0) If Cells(i, "C") <> "Failed" Then Cells(i, "D") = k End If Next End Sub

Sub vlkP_blankfild() Dim i As Long Dim k As Integer For i = 2 To Sheet2.Range("A1048576").End(xlUp).Row k = Application.VLookup(Cells(i, "A"), Sheets("sheet1").Range("A1:C" & Sheet1.Range("A1048576").End(xlUp).Row), 3, 0) If Cells(i, "D").Value = "" Then Cells(i, "D") = k End If Next End Sub

Option Explicit Sub VlookUpExampleDifferBooks() 'This example look up table in different book and sheet (TABLE 1 - ActiveSheet, TABLE 2 - Book1 and sheet1) Dim rw As Long For rw = 3 To 12 Cells(rw, 3) = Application.VLookup(Cells(rw, 2), Workbooks("Book2.xls").Sheets("Sheet1").Columns("B:C"), 2, False) Next End Sub

Option Explicit Sub VlookUpExampleDifferSheets() 'This example look up table in different sheet (TABLE 1 - ActiveSheet, TABLE 2 - Sheet 2) Dim rw As Long For rw = 3 To 12 Cells(rw, 3) = Application.VLookup(Cells(rw, 2), Sheets("Sheet2").Columns("B:C"), 2, False) Next End Sub

Option Explicit Sub VlookUpExampleSameSheets() 'This example look up table inside same sheet as picture above (TABLE 1 & 2 SAME SHEET) Dim rw As Long For rw = 3 To 12 Cells(rw, 7) = Application.VLookup(Cells(rw, 6), ActiveSheet.Columns("B:C"), 2, False) Next End Sub

{=IFERROR(INDEX($C$2:$C$25,MATCH(0,COUNTIF($E$1:E1,$C$2:$C$25),0)),"")}