Excel Lookup for VBA

oldaf294

Registered User.
Local time
Today, 07:33
Joined
Mar 29, 2002
Messages
91
Is there anyway to accomplish this Excel function in VBA?
A5 =0, returns P
A5 =1,returns Q
A5 =5,returns U
etc...
Code:
=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
 
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...

Code:
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)
 
Use Switch() function?, though Doc's ASC option is much neater?
 
Thanks, but I think I'll leave my other code alone. It works. Just thought I could be more efficient.
 
Check out Application.WorksheetFunction
 

Users who are viewing this thread

Back
Top Bottom