Sort IP Address

Option Explicit
Dim rowcount As Long, myrng As Range, cell As Object, cell1 As Object



Sub sortIPAddress()
rowcount = ThisWorkbook.Sheets(1).Range("A1").End(xlDown).Row
Set myrng = ThisWorkbook.Sheets(1).Range("A1:A" & rowcount)
    For Each cell In myrng
      cell.Offset(0, 1) = WorksheetFunction.Substitute(cell, ".", "")
      cell.NumberFormat = "#"
   
    Next
 
    ThisWorkbook.Sheets(1).Range("B1:B" & rowcount).Sort key1:=Range("B1"), Header:=xlYes
    For Each cell1 In ThisWorkbook.Sheets(1).Range("B2:B" & rowcount)
            cell1 = Mid(cell1, 1, 3) & "." & Mid(cell1, 4, 3) & "." & Mid(cell1, 7, 1) & "." & Mid(cell1, 8, Len(cell1) - 7)
         
    Next
    ThisWorkbook.Sheets(1).Range("B1:B" & rowcount).Columns.AutoFit
End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODycC1keV8wZHVUREU/edit?usp=sharing

Employee Status report in Userform with Autofilter in VBA

https://drive.google.com/file/d/0B23eJ2xd9ODya2JEUUxTVC01aGM/edit?usp=sharing

Lock/Unlock Range with Criteria

Sub showParameter()
 
    If ThisWorkbook.Sheets(2).Range("J1") = 1 Then
       With ThisWorkbook.Worksheets(1)
                .Protect Password:="123india", UserInterfaceonly:=True, DrawingObjects:=False, Contents:=True, AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True
                .Cells.Locked = False
                .Range("A1:E50").Locked = True
                .EnableSelection = xlNoRestrictions
                .EnableOutlining = True
      End With
    ElseIf ThisWorkbook.Sheets(2).Range("J1") = 2 Then
        With ThisWorkbook.Worksheets(1)
                .Protect Password:="123india", UserInterfaceonly:=True, DrawingObjects:=False, Contents:=True, AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True
                .Cells.Locked = False
                .Range("A1:E50").Locked = True
                .EnableSelection = xlNoRestrictions
                .EnableOutlining = True
      End With
    End If

End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODySE9EM1FpN3ROc3c/edit?usp=sharing

Convert Excel file to *.CSV

Option Explicit
Dim rowcount As Long, filepath As String
Sub convertoCSV()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
    rowcount = ThisWorkbook.Sheets(1).Range("A1").End(xlDown).Row
    With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Select a path to store file"
            If .Show = True Then
                filepath = .SelectedItems(1) & "\"
                'converting into text format
                ThisWorkbook.Sheets(1).Range("A:A").NumberFormat = "@"
                ThisWorkbook.SaveAs Filename:=filepath & "Somu.csv", FileFormat:=xlCSV, CreateBackup:=False
         
            Else
                MsgBox "Folder for *.CSV file not selected"
            End If
 
    End With


End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODyOVZPSHUtSDBkZ2M/edit?usp=sharing

Cut & Paste using VBA Code

Task for attached link:
https://drive.google.com/file/d/0B23eJ2xd9ODyUEtOVk4zRWt4VDA/edit?usp=sharing

Want to cut all these HQ names from column B and paste them to the very next column C

VBA Code



Option Explicit
Dim lookupval As String, myrng As Range, firstaddress As String
Sub copyHOnamelist()

lookupval = "HQ Name:"
With ThisWorkbook.Sheets(1).Range("A:A")
Set myrng = .Find(lookupval, LookIn:=xlValues)

    
    If Not myrng Is Nothing Then
        firstaddress = myrng.Address
            Do
                
                myrng.Offset(0, 1).Cut Destination:=myrng.Offset(0, 2)
                Set myrng = .FindNext(myrng)
                
                     
        
                
            Loop While Not myrng Is Nothing And myrng.Address <> firstaddress
            
    End If
End With

End Sub

VBA Code to Split Word

Sl. No.	Name with No Space	Output
1	RahulSaxena	Rahul Saxena
2	PramodSingh	Pramod Singh
3	ManishSharma	Manish Sharma
4	DhirenMathur	Dhiren Mathur
5	RavindraKumarJena	Ravindra Kumar Jena
6	RamSinghTohan	Ram Singh Tohan
7	SurjitSinghManhas	Surjit Singh Manhas

Function splitWord(myval As String)

Dim tiny As String, finalword As String

finalword = myval

    For i = 1 To Len(myval)

        

        tiny = Mid(myval, i, 1)

            If (tiny = StrConv(tiny, vbUpperCase)) Then

            finalword = WorksheetFunction.Substitute(finalword, tiny, " " & tiny)

            

            End If

    Next

    splitWord = WorksheetFunction.Trim(finalword)

End Function

Create PivotTable with Criteria In VBA

Dim pvttable As PivotTable
Private Sub worksheet_change(ByVal target As Range)
    If target.Address = ThisWorkbook.Sheets("Soum").Range("C3").Address Then
        Select Case ThisWorkbook.Sheets("Soum").Range("C3")
            Case "Daily Report"
               Call createdailyReport
            Case "Weekly Report"
               Call createdweeklyReport
            Case "Monthly Report"
                Call createdmonthlyReport
 
        End Select
     
    End If
End Sub

Sub createdailyReport()
On Error Resume Next
Application.ScreenUpdating = False
For Each pvttable In ThisWorkbook.Sheets("Soum").PivotTables
    ThisWorkbook.Sheets("Soum").Range(pvttable.TableRange2.Address).Delete

Next
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets("Dump").Range("A2").CurrentRegion.Address).CreatePivotTable tabledestination:=ThisWorkbook.Sheets("Soum").Range("C6"), tablename:="Pivottable1"
    With ThisWorkbook.Sheets("Soum").PivotTables("Pivottable1")
        .PivotFields("Date").Orientation = xlRowField
        .PivotFields("Agent Name").Orientation = xlPageField
        '
        .PivotFields("ACD Calls").Orientation = xlDataField
     
        .PivotFields("Avg ACD Time").Orientation = xlDataField
        .PivotFields("Sum of ACD Calls").Caption = "Total ACD Calls"
        .PivotFields("Sum of Avg ACD Time").Caption = "Average ACD Time"


    End With
End Sub

Sub createdmonthlyReport()
On Error Resume Next
Application.ScreenUpdating = False
For Each pvttable In ThisWorkbook.Sheets("Soum").PivotTables
    ThisWorkbook.Sheets("Soum").Range(pvttable.TableRange2.Address).Delete

Next
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets("Dump").Range("A2").CurrentRegion.Address).CreatePivotTable tabledestination:=ThisWorkbook.Sheets("Soum").Range("C6"), tablename:="Pivottable1"
    With ThisWorkbook.Sheets("Soum").PivotTables("Pivottable1")
        .PivotFields("Date").Orientation = xlRowField
        Sheets("Soum").Range("C8").Select
        Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, False)
        .PivotFields("Agent Name").Orientation = xlPageField
        '.PivotFields("ACD Calls").Orientation = xlColumnField
        .PivotFields("ACD Calls").Orientation = xlDataField
     
        .PivotFields("Avg ACD Time").Orientation = xlDataField
        .PivotFields("Sum of ACD Calls").Caption = "Total ACD Calls"
        .PivotFields("Sum of Avg ACD Time").Caption = "Average ACD Time"
       ' .PivotFields("Date").LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        'False, True, False, False)



    End With
End Sub

Sub createdweeklyReport()
'On Error Resume Next
Application.ScreenUpdating = False
For Each pvttable In ThisWorkbook.Sheets("Soum").PivotTables
    ThisWorkbook.Sheets("Soum").Range(pvttable.TableRange2.Address).Delete

Next
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets("Dump").Range("A2").CurrentRegion.Address).CreatePivotTable tabledestination:=ThisWorkbook.Sheets("Soum").Range("C6"), tablename:="Pivottable1"
    With ThisWorkbook.Sheets("Soum").PivotTables("Pivottable1")
        .PivotFields("Date").Orientation = xlRowField
        Sheets("Soum").Range("C8").Select
        Selection.Group Start:=True, End:=True, by:=7, Periods:=Array(False, _
        False, False, True, False, False, False)
        .PivotFields("Agent Name").Orientation = xlPageField
        '.PivotFields("ACD Calls").Orientation = xlColumnField
        .PivotFields("ACD Calls").Orientation = xlDataField
     
        .PivotFields("Avg ACD Time").Orientation = xlDataField
        .PivotFields("Sum of ACD Calls").Caption = "Total ACD Calls"
        .PivotFields("Sum of Avg ACD Time").Caption = "Average ACD Time"
       ' .PivotFields("Date").LabelRange.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        'False, True, False, False)



    End With
End Sub


For file:

https://drive.google.com/file/d/0B23eJ2xd9ODybUFMaEVYbnE4WDQ/edit?usp=sharing

Copy Specific Cells from files under Folder/SubFolder

Option Explicit
Dim objFso As Object, objFolder As Object, pathname As String, eachfile As Variant, objFolder1 As Object, eachfolder As Object
Dim tempcounter As Long, wb As Workbook, i As Integer, j As Integer


Sub copyFromSpecificfolderandsubfolder()
On Error Resume Next
pathname = "D:\Somu\ExcelClasses Notes"
Set objFso = New Scripting.FileSystemObject
Set objFolder = objFso.GetFolder(pathname)
Set objFolder1 = objFolder.SubFolders
Set wb = ThisWorkbook
tempcounter = 1
Call readfile(objFolder, tempcounter)

For Each eachfolder In objFolder1

Call readfile(eachfolder, tempcounter)

Next
tempcounter = 0
End Sub

Sub readfile(myFolder As Scripting.Folder, tempcounter)

For Each eachfile In myFolder.Files
    If checkExtension(eachfile) = True Then
        tempcounter = tempcounter + 1
 
        Workbooks.Open eachfile
        ActiveWorkbook.Sheets(1).Range("A2:C2").Copy wb.Sheets(1).Range("A" & tempcounter)
        ActiveWorkbook.Close
    End If
Next
End Sub

Public Function checkExtension(eachfile) As Boolean
i = InStr(1, eachfile, ".")
j = Len(eachfile)

Select Case Mid(eachfile, i + 1, (j - i))
    Case "xls"
        checkExtension = True
    Case "Xlsm"
        checkExtension = True
    Case "xlsx"
        checkExtension = True
    Case "xlsb"
        checkExtension = True
    Case Else
        checkExtension = False
End Select
End Function

Split data of a Single workbook into different workbook

Option Explicit
Dim wb As Workbook, ws As Worksheet, myrng As Range, i As Integer, x As Integer
Dim fso As Object, fldr As Object, fl As Object, item_, itemcol As Collection
Dim path As String, rawdata
Sub migrateData()
Application.ScreenUpdating = False
On Error Resume Next
path = "D:\Somu\Task12092013-3"
 Set fso = New Scripting.FileSystemObject
 Set fldr = fso.GetFolder(path)
 Set itemcol = New Collection
 'For Each fl In fldr.Files
 Set wb = ThisWorkbook
 Set ws = wb.ActiveSheet
 MsgBox ws.UsedRange.Address
 Set myrng = ws.Range(ws.UsedRange.Address)
 item_ = ws.Range("d2:" & ws.Range("d2").End(xlDown).Address).Value
  For i = 1 To UBound(item_, 1)
        itemcol.Add item_(i, 1), item_(i, 1)

  Next
  For x = 1 To itemcol.Count
    myrng.AutoFilter , field:=4, Criteria1:=itemcol(x)
    rawdata = ws.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible)
        For Each fl In fldr.Files
               If Left(fl.Name, InStr(1, fl.Name, ".") - 1) = itemcol(x) Then
                Workbooks.Open path & "\" & fl.Name
                ActiveWorkbook.ActiveSheet.Range("a" & ActiveSheet.UsedRange.Rows.Count).Resize(UBound(rawdata, 1), UBound(rawdata, 2)) = rawdata
                ActiveWorkbook.Save
                ActiveWorkbook.Close
               Else
                    Workbooks.Add
                    ActiveWorkbook.ActiveSheet.Range("a1") = "City"
                    ActiveWorkbook.ActiveSheet.Range("b1") = "Map Code"
                    ActiveWorkbook.ActiveSheet.Range("c1") = "Model"
                    ActiveWorkbook.ActiveSheet.Range("d1") = "Country"
                    ActiveWorkbook.ActiveSheet.Range("e1") = "Batch Refference"
                    ActiveWorkbook.ActiveSheet.Range("f1") = "Source"
                    ActiveWorkbook.ActiveSheet.Range("a2").Resize(UBound(rawdata, 1), UBound(rawdata, 2)) = rawdata
                    ActiveWorkbook.SaveAs path & "\" & itemcol(x), FileFormat:=56
                    ActiveWorkbook.Close
                    Exit For
               End If
 
        Next
  Next
End Sub


For file:
https://drive.google.com/file/d/0B23eJ2xd9ODydUtzYkwtaU5ZMDA/edit?usp=sharing

A Simple VBA Code for Creating PivotTable

Dim pttbl As PivotTable
Sub createPivot()
On Error Resume Next
For Each pttbl In ThisWorkbook.Sheets(2).PivotTables
        ThisWorkbook.Sheets(2).Range(pttbl.TableRange2.Address).Delete
Next

   ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets(1).Range("A1").CurrentRegion.Address). _
   CreatePivotTable TableDestination:=ThisWorkbook.Sheets(2).Range("C5"), TableName:="PivotTable1"
    With ThisWorkbook.Sheets(2).PivotTables("PivotTable1")
        .PivotFields("Processed by").Orientation = xlRowField
        .PivotFields("status").Orientation = xlDataField
        .PivotFields("Transaction type").Orientation = xlPageField
     
    End With

End Sub




https://drive.google.com/file/d/0B23eJ2xd9ODyRFgxWGhhVldGUzQ/edit?usp=sharing

Sending Attachment through OutLook

Sub sendMail
Dim outApp As New Outlook.Application, outMail As Variant,mailId as String
Set outMail = outApp.CreateItem(olMailItem)
        With outMail
            .To = mailId
            .Subject = Mid(wb.Name, 1, InStr(wb.Name, ".") - 1)
            .Body = "Hi," & vbNewLine & "     Please find the attachment" & vbNewLine & "Regards," &        vbNewLine & "Soumyendu"
            .Attachments.Add (wb.Path & "\" & wb.Name)
         
            .Display
        End With
    wb.Close


End sub

Count Colored Cells through VBA

Option Explicit
Dim myrng As Range, rowcount As Integer, criteria As Range, cell As Variant, criteriaIndex As Long
Dim tempcounter As Long
Function countColoredcells(ByVal criteria As Range) As Long
rowcount = Sheets(3).Range("A1").End(xlDown).Row
Set myrng = ThisWorkbook.Sheets(3).Range("A1:A" & rowcount)
tempcounter = 0
    criteriaIndex = criteria.Interior.ColorIndex
    For Each cell In myrng
        If (cell.Interior.ColorIndex = criteriaIndex) Then
          tempcounter = tempcounter + 1
        End If
    Next
    countColoredcells = tempcounter
End Function

VBA Code for Autofilter with dynamic criteria

Option Explicit
Dim myrng As Range
Dim rowcount As Byte
Dim cell As Variant
Dim MyArray() As Variant, temp As Integer



Sub filterwithdynamicArray()
rowcount = Sheets(1).Range("H2").End(xlDown).Row
temp = 0

Set myrng = Sheets(1).Range("H2:H" & rowcount)
    For Each cell In myrng
        temp = temp + 1
        ReDim Preserve MyArray(1 To temp)
        MyArray(temp) = cell
    Next cell
    Sheets(1).Range("A1").AutoFilter field:=2, Criteria1:=MyArray, Operator:=xlFilterValues
End Sub

File to download:

https://drive.google.com/file/d/0B23eJ2xd9ODyWVFsYVhYZkxJODQ/edit?usp=sharing

CustomPercentile Function using VBA

Option Explicit
Option Base 1
Dim notemptyrng As Boolean
Function customPercentile(myrng As Range, myval As Double) As Variant
Dim Data() As Double
Dim i  As Long, j As Long, nearestRank As Long
    If isRangeEmpty(myrng) = False Then
            customPercentile = "Input Range is Empty"
            Exit Function
    End If
    If (myval < 0 Or myval > 100) Then
        customPercentile = "Percentile must be between 0& 100"
   
    End If
    ReDim Data(myrng.Rows.Count * myrng.Columns.Count)
    For i = 1 To myrng.Rows.Count
        For j = 1 To myrng.Columns.Count
                If IsNumeric(myrng.Cells(i, j)) = True Then
                    Data(i * j) = myrng.Cells(i, j)
                Else
                    customPercentile = "Error exists in" & myrng.Cells(i, j).Address
                    Exit Function
                End If
        Next
    Next
    Call myBubbleSort(Data)
    nearestRank = Round(myval / 100 * (myrng.Rows.Count * myrng.Columns.Count) + 0.5, 0)
   
    Select Case myval
        Case 0: customPercentile = Data(1)
        Case Else: customPercentile = Data(nearestRank)
    End Select
   
End Function

Function isRangeEmpty(myrng1 As Range) As Boolean
        notemptyrng = False
        If Application.WorksheetFunction.CountA(myrng1) > 0 Then
            notemptyrng = True
        End If
        isRangeEmpty = notemptyrng
End Function

Function myBubbleSort(myArray() As Double) As Variant
    Dim i As Long
    Dim j As Long
    Dim tempval As Variant
    For i = LBound(myArray) To UBound(myArray) - 1
        For j = i + 1 To UBound(myArray)
                If myArray(i) > myArray(j) Then
                tempval = myArray(j)
                myArray(j) = myArray(i)
                myArray(i) = tempval
                End If
        Next
    Next
End Function

Updating Master File Using VBA

VBA Code for updating Master file sheet by different name from other other workbooks with same sheet name under  folder( with same sheet name)




Option Explicit
Dim fso As FileSystemObject
Dim fl As file
Dim fldr As Folder, i As Integer
Dim wb As Workbook, rowcount As Integer, rowcount1 As Integer
Sub updateSheet()
On Error Resume Next
Application.ScreenUpdating = False
Set fso = New FileSystemObject
For i = 4 To ThisWorkbook.Sheets.Count
     
    If (fso.FolderExists(ThisWorkbook.Path & "\" & Sheets(i).Name) = True) Then
     
      Set fldr = fso.GetFolder(ThisWorkbook.Path & "\" & Sheets(i).Name & "\")
      rowcount = ThisWorkbook.Sheets(i).Range("a2").End(xlDown).Row
   
        For Each fl In fldr.Files
            Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & Sheets(i).Name & "\" & fl.Name)
            rowcount1 = wb.Sheets(1).Range("A2").End(xlDown).Row
         
            wb.Sheets(1).Range("A2:T" & rowcount1).Copy Destination:=ThisWorkbook.Sheets(i).Range("A" & rowcount)
            wb.Close
            Set wb = Nothing
        Next
    End If
Next
End Sub

Convert File Format in a Folder

VBA code for converting excel files to *.xls format in a folder

Option Explicit
Dim pathname As String, fso As New Scripting.FileSystemObject, folder, fl As Object
Sub renameFiles()

      With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "Select folder"
            If .Show = True Then
                pathname = .SelectedItems(1)

            Else
                MsgBox "Folder not selected"
            End If

      End With
      Set folder = fso.GetFolder(pathname)
   
      For Each fl In folder.Files
   
           If fso.GetExtensionName(pathname & "\" & fl.Name) Like "xl*" Then
                        fl.Name = "Somu.xls"
             
           End If
      Next
      Set folder = Nothing
      Set fso = Nothing
End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODyc21jdXpkaUtLVUk/edit?usp=sharing

Update table via deleting records

Option Explicit
Dim clearedData As Range, cell As Range
Dim deletedataCounter As Byte
Sub updateDat()
    Set clearedData = Sheets(2).Range("A2:A" & Sheets(2).Range("A2").CurrentRegion.Rows.Count)
    [a1].Select
    deletedataCounter = 0
    Do
        ActiveCell.Offset(1, 0).Select
            For Each cell In clearedData
                If ActiveCell.Value = cell.Value Then
                  Range(ActiveCell.Address, ActiveCell.Offset(0, 2)).Select
                  Selection.Delete xlShiftUp
                  deletedataCounter = deletedataCounter + 1
                End If
            Next cell
    Loop Until ActiveCell.Value = ""
    MsgBox deletedataCounter & "Cheques Cleared" & [a1].CurrentRegion.Rows.Count & "Pending"
    Set clearedData = Nothing
End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODydDRQd1BhcEJDVEk/edit?usp=sharing

Copy Range from Another Workbook through VBA

Option Explicit
Dim fso As FileSystemObject
Dim fl As File
Dim fldr As Folder
Public wb As Workbook, wbnew As Workbook
Dim fldrpath As String
Sub trackSheet()
Set wb = ThisWorkbook
On Error GoTo ErrorHandler
Set fso = New Scripting.FileSystemObject
Application.FileDialog(msoFileDialogFolderPicker).Title = "Choose Folder"
Application.FileDialog(msoFileDialogFolderPicker).Show
fldrpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
Set fldr = fso.GetFolder(fldrpath)
For Each fl In fldr.Files
    wb.Sheets.Add after:=Sheets(Sheets.Count)
 
    wb.Sheets(Sheets.Count).Name = fl.Name
Call copySheet(fldrpath & fl.Name)


Next
Set wb = Nothing
Exit Sub
ErrorHandler:

MsgBox "Select Folder"

End Sub



Sub copySheet(ByVal pathname As String)
 i = InStr(pathname, ".")
 extn = Mid(pathname, i, Len(pathname) - (i - 1))
 If extn Like ".xl*" Then
        Set wbnew = Workbooks.Open(pathname)
        Application.DisplayAlerts = False
        wbnew.Sheets(1).UsedRange.Copy Destination:=wb.Sheets(wb.Sheets.Count).Range("A1")
        wb.Save
        wbnew.Close
 End If
End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODycEVOclQ5TmJkYnM/edit?usp=sharing

Application.Caller Example in VBA

Option Explicit
Dim myrng As Range
Dim shp As Shape
Sub displayData()
Set shp = ThisWorkbook.Sheets(1).Shapes(Application.Caller)

        Select Case shp.TextFrame.Characters.Text
            Case "Display Data Hiding Zero"
                  shp.TextFrame.Characters.Text = "Display all Data"
                  For Each myrng In Range("D2:D23")
                     If myrng = 0 Then myrng.EntireRow.Hidden = True
                 
                  Next
            Case Else
                  shp.TextFrame.Characters.Text = "Display Data Hiding Zero"
                  Cells.EntireRow.Hidden = False
        End Select

End Sub

For details about Application.Caller plz refer:

http://msdn.microsoft.com/en-us/library/bb220902%28v=office.12%29.aspx

Example file link

https://docs.google.com/file/d/0B23eJ2xd9ODyZXNHNktaeWlFUkU/edit?usp=sharing

Bubble Sort in VBA

Option Explicit
Dim myarray(), lookuprange As Range
Dim myrng As Range, mycell, i As Integer, k As Integer, l As Integer, tempvar

Sub sortarrangeData()
Application.ScreenUpdating = False
ReDim myarray(countUnique(Sheets(1).Range("A2:A31")))
Set myrng = ThisWorkbook.Sheets(1).Range("A2:A31")
For Each mycell In myrng
        If mycell <> mycell.Offset(1, 0) Then
            myarray(i) = mycell
            i = i + 1
         
        End If
Next
i = 0
'bubble sort
For k = 0 To countUnique(Sheets(1).Range("A2:A31")) - 1
    For l = k + 1 To countUnique(Sheets(1).Range("A2:A31"))
         If myarray(k) > myarray(l) Then
            tempvar = myarray(k)
            myarray(k) = myarray(l)
            myarray(l) = tempvar
         End If
       
       
    Next
 
Next
For l = 1 To countUnique(Sheets(1).Range("A2:A31"))
 
    Set lookuprange = ThisWorkbook.Sheets(1).Cells.Find(myarray(l), LookIn:=xlValues, lookat:=xlWhole)
    'MsgBox myarray(l) & "Addess" & lookuprange.Address
    Cells(l + 1, 5) = myarray(l)
    Cells(l + 1, 6) = lookuprange.Offset(0, 1)
    Cells(l + 1, 7) = lookuprange.Offset(1, 1)
    Cells(l + 1, 8) = lookuprange.Offset(2, 1)
Next
End Sub


Function countUnique(rng As Range) As Long
Dim coll As New Collection
Dim cell As Variant

On Error Resume Next
    For Each cell In rng
     
        coll.Add CStr(cell.Value), CStr((cell.Value))

    Next
    countUnique = coll.Count
 
    Set coll = Nothing
End Function

https://docs.google.com/file/d/0B23eJ2xd9ODyWUtvbUpJZ08wbE0/edit?usp=sharing