dmcgimpsey
Active Member
- Joined
- Mar 30, 2004
- Messages
- 268
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
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