Making VBA create a LOOKUP formula in a worksheet

[erik11]

Dear MrExcel users

My task is to create some VBA code, which after execution will place a certain formula inside a cell. You can think of the formula in terms of an exam: The formula takes as input the score of a student and gives as output the corresponding grade. If the score is less than 10, the output should be an "F", if the score is at least 10 but less than 20, the output should be an "E", ... , if the input is at least 50, the output should be an "A". So if the score is for example 32, the student should get a "C".

I can pretty easily write some VBA code which produce a formula doing the job, if I use nested IF commands. It will however be a pretty long formula and it will be hard to overview, so I am looking for something better. Then I stumpled upon the LOOKUP command. I actually made it work when I tested the formula manually in a worksheet writing the formula, assuming the input number is in cell A1:

=LOOKUP(A1,{0,10,20,30,40,50,100},{"F","E","D","C","B","A"})

NB! I hope I translated it correctly. I use a Danish version of Excel. This sometimes creates a little pain, since in the worksheets Danish words are being used and comma as decimal separator instead of a dot, and semicolon instead of a comma to separate arguments inside formulas. On the other hand inside the Excel VBA editor everything is american

Now I would like to know if this LOOKUP command is the right way to go and if so, how do I write the VBA code to produce the formula in say cell B1? I have tried doing it, but I have troubles getting it to work. The syntax I have tried is not accepted. I was not able to use the { parenteses for example ...

I hope someone can help!

Regards, Erik

 

[VoG]

Try this (keep the , as Excel should substitute ; automatically)

Sub a()
Range("B1").Formula = "=LOOKUP(A1,{0,10,20,30,40,50,100},{""F"",""E"",""D"",""C"",""B"",""A""})"
End Sub

 

[erik11]

Thanks a lot, Peter. It worked!

By by the way: Somewhere it is mentioned that one should use the command HLOOKUP instead. What is the reason for this?

Regards, Erik

 

[VoG]

I don't think that HLOOKUP would work in that type of formula.

 

[erik11]

You are probably right, that HLOOKUP and VLOOKUP don't really apply here. Maybe they are better if the target is to be compared to the content of a whole range of cells in a worksheet?

Anyway my problem is solved. Again big thanks!

Erik