Autofilling a Table that has a Changing Position

[ashperson95]

Hello all again,

I have two tables in one sheet that both change their amount of rows based on a certain user input. The first table changes rows based on a person's input by an input box, and the second table changes rows based on a certain value from the first table. The second table then fills in the info into the empty rows. However, the problem I'm having is that the code I have to fill in the second table doesn't work now that the first table is going to be constantly changing sizes, because the code is written with a fixed range of cells. I tried just putting in "Table3[Year]" or "Table3[Savings]" (the two columns in my second table) in place of the fixed range, but it doesn't work. Below is my code for the entire macro. I've bolded the line of code (in the second sub) that I need assistance with.

Could anyone help me out with this?

Sub EnterLocationInfo()Dim locations As Variant
Dim lightrows As Long
Dim locationNum As Integer
Dim fixtures As Variant
Dim hoursDay As Variant
Dim hoursWeek As Variant


hoursWeek = Application.InputBox("How many days a week does this client operate their lights?", "Days Per Week")


If Not IsNumeric(hoursWeek) Then
Do Until IsNumeric(hoursWeek)
hoursWeek = Application.InputBox("Sorry, that's not a valid entry. How many days a week does this client operate their lights?", "Days Per Week")
Loop
End If


Range("C1").Value = hoursWeek


lightrows = Range("Table2").Rows.Count
locations = Application.InputBox("How many locations do you have?", "Location Count")


If Not IsNumeric(locations) Then
Do Until IsNumeric(locations)
locations = Application.InputBox("Sorry, that's not a valid entry. How many locations do you have?", "Location Count")
Loop
End If


If locations > lightrows Then
Do Until locations = lightrows
ListObjects("Table2").ListRows.Add
AlwaysInsert = True
lightrows = Range("Table2").Rows.Count
Loop
End If


If locations < lightrows Then
Do Until locations = lightrows
ActiveSheet.ListObjects("Table2").ListRows(lightrows).Delete
lightrows = Range("Table2").Rows.Count
Loop
End If


LightTypes.ComboBox1.List = Sheets("Light Bulbs and Fixtures").Range("Table1[Type of Light]").Value


locationNum = 1
For Each InfoInput In Range("Table2[Location]")


InfoInput.Value = Application.InputBox("Name of location " & locationNum, "Name of Location")


fixtures = Application.InputBox("How many fixtures does location " & locationNum & " have?", "Fixture Count")


If Not IsNumeric(fixtures) Then
Do Until IsNumeric(fixtures)
fixtures = Application.InputBox("Sorry, that's not a valid entry. How many fixtures does location " & locationNum & " have?", "Fixture Count")
Loop
End If


InfoInput.Offset(, 1).Value = fixtures


LightTypes.Show
InfoInput.Offset(, 2).Value = LightTypes.ComboBox1.Value


hoursDay = Application.InputBox("How many hours a day do the fixtures in location " & locationNum & " run?", "Hours Per Day")


If Not IsNumeric(hoursDay) Then
Do Until IsNumeric(hoursDay)
hoursDay = Application.InputBox("Sorry, that's not a valid entry. How many hours a day do the fixtures in location " & locationNum & " run?", "Hours Per Day")
Loop
End If


InfoInput.Offset(, 3).Value = hoursDay


locationNum = locationNum + 1
Next InfoInput
MsgBox ("Light count inputs complete!")
End Sub


Sub EditRows()
Dim tablerows As Long
Dim Life As Long
Dim MyTable As ListObject
Dim Used As Range
'Gets value of Life Expectancy
Life = Range("Table2[[#Totals], [Life Expectancy Fixed]]").Value
'Counts rows in table
tablerows = Range("Table3").Rows.Count




'Sees if Life Expectancy is higher than the amount of rows, and adds rows accordingly
If Life > tablerows Then
Do Until Life = tablerows
ListObjects("Table3").ListRows.Add
AlwaysInsert = True
With ActiveSheet
        Set MyTable = .ListObjects("Table3")
        Set Used = .Range("A26:A" & .Range("A24").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
tablerows = Range("Table3").Rows.Count
Loop
End If

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

 

[ashperson95]

It's been a while and no has answered this yet. I really would appreciate some assistance.

 

[ashperson95]

This is really a pressing issue, and I would really appreciate anyone's input.