Explain about INSTR Function

     

  • It finds required letter position in STRING

    •  

     

  • In a string same letter multiple times exists, program counts first position
  • It is case sensitive., in the below mentioned example we used "a" in program, but it not identified "A"

    •  

     

    Private Sub CommandButton1_Click()
    Dim max As Integer
    max = Range(Range("A1"), Range("A1").End(xlDown)).Rows.Count
    Dim i As Integer
    For i = 1 To max
    'find "a" postion in specified string
    Cells(i, 2) = InStr(Cells(i, 1), "a")
    Next
    End Sub

     

     

     

    Example: Instr Function - - retrieve part of string

     

    Private Sub CommandButton1_Click()
    Dim i As Integer
    i = 1
    Max = Range(Range("A1"), Range("A1").End(xlDown)).Rows.Count
    For i = 1 To Max
    'through instr function we find the BLANK position
    Cells(i, 2).Value = Left(Cells(i, 1), InStr(Cells(i, 1), " ") - 1)
    Cells(i, 3).Value = Right(Cells(i, 1), InStr(Cells(i, 1), " "))
    Next
    End Sub

     

     

     

    State about InstrRev Function

     

  • It returns the position of string from reverse

    •  

    Private Sub CommandButton1_Click()
    Dim max As Integer
    max = Range(Range("A1"), Range("A1").End(xlDown)).Rows.Count
    Dim i As Integer
    For i = 1 To max
    'find "a" postion in specified string
    Cells(i, 2) = InStrRev(Cells(i, 1), "a")
    Cells(i, 3) = InStr(Cells(i, 1), "a")
    Next
    End Sub

     

     

     

    Len Function

     

  • Denotes about the length of a string

    •  

    Private Sub CommandButton1_Click()
    MsgBox Len("sriguranjani")
    Range("B1") = Len(Range("A1"))
    End Sub

     

     

    Rank Function

     

  • It Ranks the data

    •  

  • It gives duplicate Ranks for UNIQUE data

    •  

    Private Sub CommandButton1_Click()
    Dim I As Integer
    Max = Range("B1").End(xlDown).Row
    Dim data As Range
    Max = Range("B1").End(xlDown).Row
    Set data = Sheets("sheet2").Range("B1:B" & Max)
    For I = 2 To Max
    Cells(I, 3).Value = Application.WorksheetFunction.Rank(Cells(I, 2), data)
    Next
    End Sub

     

     

    LCASE Function

     

     

     

  • It converts the data into lower case

    •  

    Example

     

     

    Copy the Code:

    Private Sub CommandButton1_Click()
    Dim max As Integer
    max = Range(Range("A1"), Range("A1").End(xlDown)).Rows.Count
    Dim i As Integer
    For i = 1 To max
    Cells(i, 2) = LCase(Cells(i, 1))
    Next
    End Sub

     

     

    LCASE & Ucase Functions

     

    Ucase

    Private Sub CommandButton1_Click()
    For Each cell In UsedRange.Cells
    cell.Value = UCase(cell.Value)
    Next
    End Sub
    Lcase
    Private Sub CommandButton2_Click()
    For Each cell In UsedRange.Cells
    cell.Value = LCase(cell.Value)
    Next
    End Sub

     

     

    Toogle case

     

    Private Sub CommandButton1_Click()
    Dim i As Integer, script
    For i = 1 To 16
    script = Cells(i, 1).Value
    If Mid(script, 1, 1) = LCase(Mid(script, 1, 1)) Then
    Cells(i, 1) = UCase(Cells(i, 1))
    ElseIf Mid(script, 1, 1) = UCase(Mid(script, 1, 1)) Then
    Cells(i, 1) = LCase(Cells(i, 1))
    End If
    Next
    End Sub

     

     

    Upper Function

     

     

     

  • It converts the text into Upper case

    •  

     

    Private Sub CommandButton1_Click()
    Dim max As Integer
    max = Range(Range("A1"), Range("A1").End(xlDown)).Rows.Count
    Dim i As Integer
    For i = 1 To max
    Cells(i, 2) = UCase(Cells(i, 1))
    Next
    End Sub

     

     

     

    Proper Function

     

     

     

  • It converts first letter into CAPITAL LETTER

    •  

     

     

    Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim max As Integer
    max = Range(Range("A1"), Range("A1").End(xlDown)).Rows.Count
    For i = 1 To max
    Cells(i, 2) = WorksheetFunction.Proper(Cells(i, 1))
    Next
    End Sub

     

     

     

    Proper Function through Selction:

     

    Private Sub CommandButton1_Click()
    Dim i As Variant
    Dim rng As Range
    Set rng = Application.InputBox("select_range", Type:=8)
    For i = 1 To rng.Cells.Count
    rng.Cells(i, 2) = WorksheetFunction.Proper(rng.Cells(i, 1))
    Next
    End Sub

     

     

    LEFT Function

     

     

     

  • We can retrieve the data from left to right

    •  

     

    Copy the Code

     

    Private Sub CommandButton1_Click()
    Dim max As Integer
    max = Range(Range("A1"), Range("A1").End(xlDown)).Rows.Count
    Dim i As Integer
    For i = 1 To max
    Cells(i, 2) = Left(Cells(i, 1), 3)
    Next
    End Sub

     

     

     

    Explain about RIGHT Function

     

  • It retrieves the data\Text from RIGHT to LEFT

    •  

    Code:

     

    Private Sub CommandButton1_Click()
    Dim max As Integer
    max=Range(Range("A1"),Range("A1").End(xlDown)).Rows.Count
    Dim i As Integer
    For i = 1 To max
    Cells(i, 2) = Right(Cells(i, 1), 3)
    Next
    End Sub

     

     

    Remove Last Character from Right

     

  • This program is useful to remove last character from right

    •  

     

    Copy the Code:

     

    Private Sub CommandButton1_Click()
    Dim max As Integer
    max = Range(Range("A1"), Range("A1").End(xlDown)).Rows.Count
    Dim i As Integer
    For i = 1 To max
    'MsgBox Len(Cells(i, 1)) - 1
    Cells(i, 2) = Left(Cells(i, 1), Len(Cells(i, 1)) - 1)
    Next
    End Sub

     

     

    Trim Function

     

  • It removed blank spaces

    •  

    Private Sub CommandButton1_Click()
    Max = Range("B1").End(xlDown).Row
    For i = 1 To Max
    Cells(i, 2) = Application.WorksheetFunction.Trim(Cells(i, 2))
    Next
    End Sub

     

     

     

     

    Trim Function - Selection through INPUTBOX

     

    Private Sub CommandButton1_Click()
    Dim max As Range
    Dim i As Integer
    Set max = Application.InputBox("Select the Range", Type:=8)
    For i = 1 To max.Cells.Count
    Cells(i, 2) = Application.WorksheetFunction.Trim(Cells(i, 2))
    Next
    End Sub

     

     

     

Hi Everyone,
Welcome to VBA Macros Tutorial. This Tutorial provides comprehensive knowledge on VBA Macros to the user. Hope it enhances your coding skills. If you like this web page kindly Share this to your near and dear. I Prepared this content as per my level of knowledge, if any changes required kindly bring to my notice. Thanks for visiting this web page

Thanks,
Pavan Kumar Gundlapalli