• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • Last Modified:

Compare and Insert in Range

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
compareAndinsert.xlsx
0
Rayne
Asked:
Rayne
  • 3
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
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
0
 
RayneAuthor Commented:
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...
0
 
RayneAuthor Commented:
the icol and the match..
0
 
Saqib Husain, SyedEngineerCommented:
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

=match(e2,e5:e12)

in the worksheet. This formula has been used in the VBA and modified accordingly.
0
 
RayneAuthor Commented:
Thank you :)
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now