searching a range alphabetically

[mshbhwn98]

Hi,

I have a named dynamic ranged on my spreadsheet called myrange. B3 is the top left hand corner. Column B is sorted alphabetically. Above the range I have an example of one row from this range which I intend the user to fill out then press a button to insert it into the row at the correct alphabetical position. Unfortunately the person who designed the spreadsheet did not think about things like this as I cannot simply add a row at the bottom and then sort it as they have merged cells in the range. (I know that's not ideal but I have to deal with the spreadsheet I've been given). My question is how do I search alphabetically through column B starting from B3 until the point in which the value in B1 alphabetically fits?

I hope that makes sense. If anything needs clarifying please let me know.

Regards

[mancubus]

hi.

Col B must be sorted ascending for the following.
change ranges to suit.



Dim insRow As Long
insRow = Application.Match(Range("B1"), Range("B1:B1000"), 1) + 1 '+1 is for the next row of the matching row
Rows(insRow).Insert
Range("B" & insRow).Value = Range("B1").Value

[snb]

msgbox columns(2).find("abcde",cells(3,2),,1).address

[mshbhwn98]

Thank you that worked great