ElseIf Structure

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

Multiple-line statements - Syntax

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_condition_n] ->  an expression (could be numeric or string)  which evaluates to True or False (note: a Null elseif_condition is equated to False). It is necessary to specify this if ElseIf is present.

elseif_statements  [elseif_statements_1 … elseif_statements_n] -> one or more statements (block of code) get executed if theelseif_condition evaluates to True. If elseif_statements are not specified, then no code will be executed if the elseif_condition evaluates to True.

Else  -> condition and elseif_conditions are tested in the order they are mentioned and if any one evaluates to True, its respective statements get executed and no subsequent condition is tested thereafter. If no previous condition or elseif_condition evaluates to True,Else clause comes into play and the else_statements get executed. It is Optional to include Else in the If...Then...Else statement.

else_statements  ->  one or more statements (block of code) get executed if no previous condition or elseif_condition evaluates to True. Ifelse_statements are not specified, then no code will be executed if it was applicable based on the conditions.

End If  ->  terminates the If…Then…Else block of statements and it is necessary to mention these keywords at the end.

Nesting:

If…Then…Else block of statements can be nested within each other and also with Select...Case statement and VBA Loops (as inner or outer loop), without any limit. It may be noted that in re. of spreadsheet functions, Excel 2003 only allows 7 levels of nesting of functions, while Excel 2007 allows up to 64

SpecialCells method to find Last Used Column in worksheet

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

UsedRange method to find number of used columns in a worksheet

Sub UsedColumns_UsedRange()

Dim usedColumns As Integer

usedColumns = ActiveSheet.UsedRange.Columns.Count

MsgBox usedColumns

End Sub

UsedRange method to find number of used rows in a worksheet

Sub UsedRows_UsedRange()

Dim usedRows As Long

usedRows = ActiveSheet.UsedRange.Rows.Count

MsgBox usedRows

End Sub

End(xlUp) method to determine Last Row with Data, in one column

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

largest value in range

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

Smallest Value in Range

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

Remove Special Characters

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

Rank If Passed

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

Lookup If Blank

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

3) Table 1 and 2 in different books and different sheets.

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

2) Table 1 and 2 in different sheets.

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

1) Table 1 and 2 same sheet.

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

Unique Desgnation

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