Adding or Subtracting Rows in a Table Based on a Cell Value

[ashperson95]

Okay, I am trying to create a program that creates a table, and then if the value of a certain cell is larger or smaller than the amount of rows already in the table (the table has a standard 10 rows with data), then it adds or subtracts based on that value. I'm not very familiar with VBA language, but I do have some background in Java programming (although it's been several years). I've only gotten to the adding rows part before I came up with an error message and I can't figure out what I did wrong.

Here is what I have so far:

Sub CreateTable()
'Creating a table
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$7:$B$17"), , xlYes).Name = "Table1"
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub


Sub EditRows()
Dim rows As Long
Dim Life As Long
'Gets value of Life Expectancy
Life = Worksheets("Sheet1").Cells(2, "D").Value
'Counts rows in table
rows = Range("Table1").rows.Count


If Life > rows Then Data.ListObjects("Table1").ListRows.Add
'This line gives me a 424 Object Required message
End Sub

Like I said, I am unfamiliar with VBA language, so is my syntax wrong or am I just completely wrong?

 

[ashperson95]

Here is what my chart looked like:

Year	Savings
1	20
2	22
3	24
4	26
5	28
6	30
7	32
8	34
9	36
10	38
11	20
12	22
13	24
14	26
15	28

<tbody>
</tbody>

And here is the whole code:

Sub CreateTable() 'Creating a tableActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$7:$B$17"), , xlYes).Name = "Table1"
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub




Sub EditRows()
Dim rows As Long
Dim Life As Long
'Gets value of Life Expectancy
Life = Worksheets("Sheet1").Cells(2, "D").Value
'Counts rows in table
rows = Range("Table1").rows.Count




'Sees if Life Expectancy is higher than the amount of rows, and adds rows accordingly
If Life > rows Then
Do Until Life = rows
ListObjects("Table1").ListRows.Add
AlwaysInsert = True
rows = Range("Table1").rows.Count
Loop
End If




'Sees if Life Expectancy is lower than the amount of rows, and deletes rows accordingly
If Life < rows Then
Do Until Life = rows
ActiveSheet.ListObjects("Table1").ListRows(rows).Delete
rows = Range("Table1").rows.Count
Loop
End If
End Sub


Sub FillTable()
    Dim MyTable As ListObject
    Dim Used As Range
    With ActiveSheet
        Set MyTable = .ListObjects("Table1")
        Set Used = .Range("A8:A" & .Range("A7").End(xlDown).Row).Resize(, 2)
        If MyTable.ListRows.Count > Used.rows.Count Then
            Used.AutoFill MyTable.DataBodyRange
        End If
    End With
End Sub

Did I miss type something?

 

[ashperson95]

Oh, I think I forgot to mention that the Savings Column has a formula in it rather than just counting. So the first row is just the number 20, but the second is =B8+2 and so on for the rest.

 

[Andrew Poulsom]

In that case try:

Set Used = .Range("A9:A" & .Range("A7").End(xlDown).Row).Resize(, 2)

 

[ashperson95]

With that I get a runtime error: Application-defined or object-defined error

 

[ashperson95]

Still need assistance with this. I've been trying out different things and nothing really works.

 

[Andrew Poulsom]

I should have tested it, sorry:

Sub FillTable()
    Dim MyTable As ListObject
    Dim Used As Range
    With ActiveSheet
        Set MyTable = .ListObjects("table1")
        Set Used = .Range("A9:A" & .Range("A7").End(xlDown).Row).Resize(, 2)
        If MyTable.ListRows.Count > Used.Rows.Count Then
            Used.AutoFill MyTable.DataBodyRange.Offset(1).Resize(MyTable.DataBodyRange.Rows.Count - 1)
        End If
    End With
End Sub