Re: Add Letter To Number
How about something like this:
Private Function NewVarianceLetter(lngQuoteNum As Long, _ Optional rngQuote As Range) As String Dim i As Long Dim rngFind As Range If rngQuote Is Nothing Then _ Set rngQuote = ActiveSheet.Cells Set rngFind = rngQuote i = 64 On Error Resume Next Do Until rngFind Is Nothing i = 1 + i Set rngFind = rngQuote.Find(What:=lngQuoteNum & Chr(i), LookAt:=xlWhole, _ MatchCase:=False, SearchFormat:=False) Loop On Error GoTo 0 NewVarianceLetter = Chr(i) End Function
The argument lngQuoteNum is the number you want to append, and the optional argument rngQuote is the range where the numbers are listed. If you omit this argument, the function will check the entire sheet, and will probably be somewhat slower. The function will return the next available letter as string, counting from A. So if you have the items 1800A, 1800B and 1800D already set, the function will return 1800C, not 1800E.
You can call it either like
There is probably a more elegant solution, but this should do the trick.