Excel Lookup for VBA (1 Viewer)

[oldaf294]

Is there anyway to accomplish this Excel function in VBA?
A5 =0, returns P
A5 =1,returns Q
A5 =5,returns U
etc...

=LOOKUP(LEFT($A$5,1),{"0","1","2","3","4","5","6","7","8","9"},{"P","Q","R","S","T","U","V","W","X","Y"})[/CODE

 

[The_Doc_Man]

In what utility? In Access you would create a two-column table where one column was the digits and the other column was the letters. That would give you whatever number/letter combo you wanted. But if you are not in Access, you have to try something else.

A function like this MIGHT work...

PUBLIC FUNCTION OldafLookup( d as number ) AS STRING
    SELECT CASE d
        CASE 0
            RETURN "P"
        CASE 1
            RETURN "Q"
        CASE 2
            RETURN "R"
...
        CASE ELSE
           RETURN "?"
    END SELECT
END FUNCTION

Then you would call it via OldafLookup(2) or something like that.

If it was always that exact set of digits and letters, never varying, you could also do this:

MyChar = CHR$( Digit + ASC("P") ) (if digit is actually numeric) or
MyChar = CHR$( Val(Digit) + ASC( "P" ) ) (if digit was a digit character, i.e. text)

 

[Gasman]

Use Switch() function?, though Doc's ASC option is much neater?

 

[oldaf294]

Thanks, but I think I'll leave my other code alone. It works. Just thought I could be more efficient.

 

[Isaac]

Check out Application.WorksheetFunction