Dec 01 2020 02:15 AM - edited Dec 01 2020 02:48 AM
I have a table with repeating entries in column 1. i have a button which adds data adjacent to each match of column 1. this works thanks, @Craig Hatmaker you are amazing.
If there is already data in the adjacent columns and i want to add a new data entry for the same match then i would like the information displayed in a row beneath the row with data(leaving a blank space below the first column match followed by the data in column 2 and 3). this is as a pose to overwriting the data in the adjacent cells which is what happens currently.
I have made an attempt and a new row is entered but i cannot get the row to be entered at the right place. also, i made an error in syntax for calling the table to have a row added to it, any help is greatly appreciated
Dec 01 2020 07:32 AM
Thanks for the kind words.
Before trying to solve the problem, let's learn about tables in VBA. In VBA they are ListObjects.
Dim oLo as ListObject
Getting (Setting) ListObjects
ListObjects belong to the worksheet's ListObjects collection. We can select the table from the worksheet's ListObjects collection in several ways. Here are 3.
Set oLo = ThisWorkbook.Sheets("Sheet1").ListObjects(1)
' or
Set oLo = Sheet1.ListObjects(1)
' or
Set oLo = Sheet1.ListObjects("Table1")
Note that because a ListObject is an object we must use Set.
We can also select a table by through a range's ListObject property. Any cell occupied by the table will return the ListObject like so (Assume A5 is in the table):
Set oLo = Sheet1.Range("A5").ListObject
Now that we know ranges occupied by ListObjects can return the ListObject, we can leverage my preferred method: getting a ListObject by Table name.
Note that table names MUST be unique within a workbook. Excel enforces this rule by tracking table names in its names collection, but the name does not refer to the ListObject, It refers to the table's range. Another odd note is the table name is not accessible from the workbook's names collection directly. We must use VBA's EVALUATE method to evaluate the name and return its range.
Set oLo = EVALUATE("Table1").ListObject
So in the above, we evaluated the name "Table1" which returned the range that Table1 occupies and that range has a ListObject property that returns the ListObject in those cells. Lastly, there is an abbreviation for EVALUATE which is [ ]. This is how I get ListObjects:
Set oLo = [Table1].ListObject
ListObject Properties
One of the wonderful things about tables is they expand. That means Excel must keep track of how many rows and columns a ListObject has.
Dim lRow as Long
Dim lCol as Long
lRow = oLo.ListRows.Count
lCol = oLo.ListColumns.Count
Thus, we can loop through all table rows like so
For lRow = 1 to oLo.ListRows.Count
...
Next
And because the ListObject's ListRows is a collection of ListRow objects, we can loop through it using VBA's FOR/EACH:
Dim oLr as ListRow
For Each oLr in oLo.ListRows
...
Next
Each ListRow has an Index (row #) and a collection of cells. Thus we can set the cells in your table like so:
If Not IsEmpty(oLr.Range(2) Then Set oLr = oLo.ListRows.Add(oLr.Index, TRUE)
oLr.Range(2).Value = Me.TextBox2.Value
oLr.Range(3).Value = Me.TextBox3.Value
Okay, that should give you some background on ListObjects and a bit on how to more efficiently address cells and ranges. Now let's go back to your problem. Let's begin with what we learned last time and build on it.
Now I'm not entirely sure I understand the problem. My assumption is we want to
The code below does this. I've added comments to help explain what each section does. Let me add a bit more explanation below
Private Sub CommandButton1_Click()
' Declare Variable
Dim oSelection As Range
Dim oArea As Range
Dim oRow As Range
Dim lRow As Long
Dim lLastRow As Long
Dim oLo As ListObject
' Initialize Variables
lLastRow = 0
Set oLo = [Table1].ListObject
' Select rows
Application.ScreenUpdating = False
[Table1].AutoFilter Field:=1, Criteria1:=Me.TextBox1.Value
Set oSelection = [Table1].SpecialCells(xlCellTypeVisible)
[Table1].AutoFilter Field:=1
Application.ScreenUpdating = True
' Process each area in selection and each row in each area
For Each oArea In oSelection.Areas
For Each oRow In oArea.Rows
' Calculate table row #
lRow = oRow.Row - oLo.HeaderRowRange.Row
' Process only if we haven't already
If lLastRow <> lRow Then
' If empty, add a new row
If Not IsEmpty(oRow.Cells(2)) Then
lRow = oLo.ListRows.Add(lRow + 1, True).Index
Set oRow = oLo.ListRows(lRow).Range
oRow.Cells(1) = Me.TextBox1.Value
End If
' Add data from form
oRow.Cells(2) = Me.TextBox2.Value
oRow.Cells(3) = Me.TextBox3.Value
DoEvents
' Remember what we just processed
lLastRow = lRow
End If
Next
Next
UserForm1.Hide
End Sub
oArea and oRow
When we select filtered ranges there is a good chance the selection will not be contiguous. If that happens there will be multiple areas in the selection and each area could have multiple rows. Whether the selection is contiguous or not, looping through selections by areas and then by rows is always the safe approach.
lLastRow
When we add rows, we change the shape of our selection so the next iteration through the loop will bring up the row we just added and if we process that row we will create a never ending loop that processes new row after new row until we reach Excel's row limit or use up all our memory. So we must remember which row we last processed, and if it is the same row, due to adding a row, skip it.
Hope that helps.
Dec 01 2020 09:08 AM
Hi Craig.
I hope you are keeping well. That was quite a master class!
I must admit, I tend to go for quick and dirty solutions such as
Set oLo = Sheet1.ListObjects(1)
without necessarily knowing for sure which sheet is sheet1 or which table is the first in the collection (though it is pretty obvious if one guesses wrongly). I don't think I ever understood why the ListObjects Collection should have a sheet as its Parent rather than the Workbook.
I think I will convert to
Set oLo = [Table1].ListObject
which has the advantage of addressing the Table by name rather than index and is readable.
Dec 01 2020 11:41 AM
Hi Peter,
Thanks for the kind words.
Yes, I'm doing well. Had a great Thanksgiving with my wife, son, daughter, and new son-in-law. My oldest daughter couldn't make it :(. Had a deep fat fried turkey in peanut oil. Makes the bird quite juicy!
Hope you and your wonderful family are doing well too.
I like this for the reasoned you mentioned and that I don't care which worksheet it is on. I can move it to any worksheet and this will always find the table.
Set oLo = [Table1].ListObject