Writing a sub function in VBA for Excel


  • I need help writing a Sub function in VBA for an Excel file.

    Specific problem:

    I have a workbook called "invoicing" with two worksheets labeled "ledger" and "acronyms."

    Each cell in column A on worksheet "ledger" is to be filled in by the user.
    The adjacent cell, in column B, on worksheet "ledger" should be automatically filled in by a sub function (specific to only that worksheet).

    Cells in column A on worksheet "acronyms" have a list of acronyms,
    and the adjacent cell, in column B, on worksheet "acronyms" has the full representation of the corresponding acronym spelt out.

    The sub function should take in the user input for each cell in column A, worksheet "ledger", and search for the representation of that acronym from column B on worksheet "acronyms." It should then put the representation into the respective/adjacent cell in column B on worksheet "ledger."

    I assume that I will use the VLOOKUP function, but I'm not familiar enough with VBA to write a subscript for it. Can anyone help me write this piece of code?

    I had written a " function" to do it within the cell itself, but here are the two issues I've been having:

    1. Each time that a new entry is added by the user, the previous entry has to be clicked on and the format box pulled down. If the format box is pulled down in advance (beyond any cells that currently have entries) then when the file is printed all the formated cells with no current values print also creating multiple blank pages being printed.

    2. Placing =IF(A2 <> "",VLOOKUP(Ledger!A2,Acronyms!A:B,2,),"") in the cell doesn't allow the data to be sorted. When the data is sorted, the values stay the same and then I have acronyms that are displaying the incorrect representations.


    Thanks in advance for any help...

    Monday, January 28, 2008 8:14 PM