Macro to INSERT a STRING in SEQUENCE or as the LAST entry

Its been a while,

I have a column of order references so: MVC01, MVC02, MVC03, MVC03A, MVC04, MVC04A

I want to be able to add a new entry in order, so MVC02A goes after MVC02. or if it is a new entry goes to the end. I want to insert a new row as opposed to move cells down.

ive looked at match functions but that is using numbers, I saw a crazy long function for a string but thought there must be an easy way????

this is what I have:

Private Sub locaterow()


Dim ianswer As String
Dim i%, m

ianswer = InputBox("oi")

With Range("A:A")
    i = .Row

    m = Application.Match(ianswer, .Value, 1)
    
    m = Application.Match

    If Not IsError(m) Then i = .Cells(1, 1).Offset(m).Row
End With

MsgBox i


End Sub

any help much appreciated


Thanks

Jim

What other possible combinations will there be? Is there likely to be MVC105? MVC1034? or only 2 digit numbers after MVC? Will it be MVC02A, 02B, 02C etc up to 02Z? 02AA?

I have a feeling you be able to simply add the new entry to the bottom and then sort the data -that would work for the example provided so far I think.
e.g.

ianswer = inputbox("oi")
Range("A" & rows.count).end(Xlup).row.offset(1,0) = ianswer
Columns(1).sort Range("A1")

They will always be MVC but could go above 100 doubt full into the 1000's.

hadn't really thought about sorting them. not sure how id sort the entire rows as opposed to the column.

For future reference tho how would I work this??


Thanks

activesheet.usedrange.sort Range("A1")

maybe?

It would give you 100 before 20 though. You would need to create a helper column with just the number and a helper column with just the letter. Then sort by the number column first and then the letter column.

Is there no way to do this using the MATCH method?

Ive also realised that it is the same issue when I come to organise my sheets, how can I put them in order??

Thanks

Probably but it would be very complicated, using sort would be easier.

Thanks for the help decided your right its easier to relabel everything to 001 and hope it doesn't go over a 1000 lol

Thanks again