ListRows.Add - changes formula in vlookup

[dmcgimpsey]

Hi Folks:

I have a table in a tab that I manipulate to add a couple of rows (default values) for a validation list.

Here is the scenario:

The table has two default rows at the top of the box, and two columns. The default rows are used to allow some values to appear in a validation list on a different cell.

I also have a formula that points to the drop down value and refers to the corresponding entry to calculate the rate for a time keeper when entering values.

for instance, on an invoice, each person has a rate assigned. If user1 has rate $50.00 - then that value is autopopulated in the rate column via a formula.

The problem I have is that when you press a "Clear" button - it removes all the entries in the Table, and adds two default rows.

Here is the code for that:

Sub newrow()


Set tbl = Sheet1.ListObjects("Table1")
Dim newrow As ListRow

Set newrow = tbl.ListRows.Add
With tbl.ListRows(1)
.Range(1) = "Provided Discount"
End With

Set newrow = tbl.ListRows.Add
With tbl.ListRows(2)
.Range(1) = "Math Error Correction"
.Range(2) = 0
End With

Sheets("PDF").Range("TimeKeeper_Range").Select


With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Parameters!$A$11:$A$50"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With


End Sub

As you can see, I add two rows at the top of the table.

The problem exists on the first sheet (invoice sheet) because when I add two rows to the lookup table, the formula automatically shifts down two cells as though I wanted to start at a new range in the table.

Here is the formula:
=IFERROR(IF(OR(D14="EXPENSE", C14="Provided Discount"),J14/H14, IFERROR(VLOOKUP(C14,Parameters!$A$11:$B$47,2,FALSE),"")),"")


The value $A$11 changes to $A$13 whenever the new rows are added. I thought that the $ would anchor the values, and prevent them from changing

Is there an option I can turn off to prevent the formula from taking the relative position of the table ? I know from debugging that it is the "ADD NEW ROW" code that causes this effect

Thanks

Don McGimpsey