Wrong data type error in consolidated code, but not in separated code

Question

Been devising a way to count from 1-999 using just two characters.

The way it goes is, 1-100 is denoted by "01-00" (00 being 100), 101-776 is denoted by AA through ZZ, 777-999 is denoted by A0, A1, A2, etc. through W2 (999).

code so far is =IF(AND(ISNUMBER(NUMBERVALUE(MID(O17,6,2))),NUMBERVALUE(MID(O17,6,2))=0),100,IF(ISNUMBER(NUMBERVALUE(MID(O17,6,2)))>0,MID(O17,6,2),IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},MID(O17,6,2)))=1,COLUMN(INDIRECT(MID(O17,6,1)&1))*10+MID(O17,7,1)+767,COLUMN(INDIRECT(MID(O17,6,2)&1))+74))) to be extracted from a string e.g. 0AAV0AE23A (AE's equivalent value would then be 105)

I've already checked the formula about 50 times, and tried to separate each IF function and they work fine individually (given that their conditions are met). The #VALUE! only returns when I consolidate the IF functions into one single formula.

If functions and their corresponding ranges:
IF(AND(ISNUMBER(NUMBERVALUE(MID(O17,6,2))),NUMBERVALUE(MID(O17,6,2))=0),100 for 100 alone IF(ISNUMBER(NUMBERVALUE(MID(O17,6,2)))>0,MID(O17,6,2) for numbers 1-99 IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},MID(O17,6,2)))=1,COLUMN(INDIRECT(MID(O17,6,1)&1))*10+MID(O17,7,1)+767,COLUMN(INDIRECT(MID(O17,6,2)&1))+74) for 777-999, with the False value being for 101-776

I would also appreciate if there was a simple way to write the code but really my main concern is to find out why the #VALUE! return keeps on showing up.

EDIT: mistake in denotation of 777. It should be A0, not A1 (already edited accordingly)

user_8759927 · Answer

Please test following formula and see if it gives desired results:

=IF(ISNUMBER(MID(O17,6,2)+0),IF((MID(O17,6,2)+0)=0,100,MID(O17,6,2)+0),IF(ISNUMBER(MID(O17,7,1)+0),COLUMN(INDIRECT(MID(O17,6,1)&1))*10+MID(O17,7,1)+767,100+(CODE(MID(O17,6,1))-65)*26+(CODE(MID(O17,7,1))-64)))

To answer second part of your question, please check your condition for 100, using formula evaluate option i.e.

AND(ISNUMBER(NUMBERVALUE(MID(O17,6,2))),NUMBERVALUE(MID(O17,6,2))=0)

which evaluates to #VALUE! error for the string sample you've posted i.e. 0AAV0AE23A.

user_2474656 · Answer

This assumes that A1 corresponds to 777 and that A0 corresponds to 786. First create a single-column table starting at cell Z1 . (any other starting location can be used):

Sub CodeMaker()
    Dim i As Long, N As Long, Digits(1 To 10) As String
    Dim Letters(1 To 26) As String, j As Long, k As Long
    
    For i = 1 To 9
        Digits(i) = CStr(i)
    Next i
    Digits(10) = "0"
    
    For i = 1 To 26
        Letters(i) = Chr(i + 64)
    Next i
    
    Range("Z:Z").NumberFormat = "@"
    
    For i = 1 To 99
        Cells(i, "Z").Value = Format(i, "00")
    Next i
    
    Cells(100, "Z").Value = "00"
    
    j = 1
    k = 1
    For i = 101 To 776
        Cells(i, "Z").Value = Letters(j) & Letters(k)
        k = k + 1
        If k = 27 Then
            k = 1
            j = j + 1
        End If
    Next i
    
    j = 1
    k = 1
    For i = 777 To 999
        Cells(i, "Z").Value = Letters(j) & Digits(k)
        k = k + 1
        If k = 11 Then
            k = 1
            j = j + 1
        End If
    Next i
        
        
End Sub

Once this is in place, all you need is a simple MATCH() formula.

With 0AAV0AE23A in A1, in A2 enter:

=MATCH(MID(A1,6,2),Z:Z,0)

NOTE:

Once the single-column table is in place, it can also be used to convert a three digit number to a two character code by using INDEX().

Wrong data type error in consolidated code, but not in separated code

2 Answers