Compare and Insert in Range

Posted on 2012-09-14
Last Modified: 2012-09-15
Hello All,

Iterate through the range and insert correctly in ascending order of ASCII order

Cell E2 will have a name. A vba macro will go through all the cells if the range “myRange”
And compare the cell [E2] value to the the current cell in the named range – if the value is less or more in ascending order, it will insert that value into the named range accordingly
For example – when E2 = “hhh”, it gets inserted between Charlie and Naren

Thank you
Question by:Rayne
    LVL 43

    Accepted Solution

    Sub InsSorted()
        If Evaluate("E5>E2") Then icol = 0 Else icol = WorksheetFunction.Match(Range("E2"), Range("E5", Range("E5").End(xlToRight)))
        Range("e5").Offset(, icol).Insert Shift:=xlToRight
        Range("e5").Offset(, icol) = Range("E2")
    End Sub

    Author Comment

    Perfect and thank you  ssaqibh,

    This works and i am rewarding you the points to make this resolution. If you would explain whats going on in your code - I was not able to decipher it clearly...that would be greatly appreciated...

    Author Comment

    the icol and the match..
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    icol returns the column number from where the data needs to be shifted right to accommodate the new value.

    This can be checked by using the formula


    in the worksheet. This formula has been used in the VBA and modified accordingly.

    Author Comment

    Thank you :)

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now