Avoiding Formulas In Many Rows

I have a simple spreadsheet that will be used by others for records management (rehabilitation case management). I have entered the code for most of the 'stuff' that I need to make the workbook easy and simple to use, and get the data back to head office each month.

Because the users will be emailing their records and I haven't yet worked out a way of sending just the "open cases", I need to keep the workbook/worksheets small (and to maintain good programming).

In column L, I have copied this formula from L2:L501:

=IF(ISBLANK($K2),"",VLOOKUP($K2,LOCSTAT!$A$1:$N$299,2,FALSE))

Column K is where the case-managed person works and so column L returns the geographical location value. Obviously it's blank until the case is opened by starting a new record and entering each field with data. For example, the user can select " Finance" from a validated drop-down list and the adjacent cell (to the right) will look up the value and return "Parramatta".

How do I translate the cell formula to some VBA?

I suspect that the static VLOOKUP range in the formula (LOCSTAT!$A$1:$N$299) will need to be dynamic range as well - I can follow the instructions for a single column dynamic range, but I am also getting lost working out how I can create a dynamic range for all data on a dedicated worksheet.

I'll be able to translate a solution to other cells as well