Putting User Input data into the ActiveCell.Row in Named Range.

[Kiyoshi]

For my data entry form, every entry goes on a new row. Sometimes certain fields/cells are left blank, so always posting to the lowest open row is not an optimal solution. I've named a range for a field that always has data (the staff member who does the initial entry) and using the following, I'm able to always have the form enter the data on the next fully blank row.*

For simplicity's sake, the sample is just having it drop "Staff Name" and "1" in the cells in question. In actuality, it would pull data from text boxes or put a 1 if a check box were checked, etc.

ActiveSheet.Range("RangeStaff").End(xlDown).Offset(1,0).Select

Range("A" & ActiveCell.Row)).Value = "Staff Name"
Range("B" & ActiveCell.Row)).Value = 1

I would like to know if I can make the form more adaptable, however. I'd like to do this using named Ranges for each column. For example, if the company has a reason to change the spreadsheet (they need to track a new factor or somesuch) and add or remove Columns, then Column letters might change, but the Range Name should stay the same. Furthermore, I would also like to transfer the code to very similar data entry forms that go into different files (due to belonging to different grants which want it to go into their specific form). The order of the information in each grant's spreadsheet is different, but the types of Data are extremely similar, such that I could name the ranges the same things and reuse most of the UserForm.

Even naming all of A as "RangeStaff" It's not possible to use:

Range("RangeStaff" & ActiveCell.Row)).Value = "Staff Name."

or anything along those lines.

What is the simplest method to add data to the the ActiveRow and a NamedRange (Which is, for all functional purposes, a named Column)?

---
* I've also noticed this doesn't work if there's no data or only data in A1. As soon as there's something in A2 or further down in A than that, it works fine. I wonder why is that so?