@Linesol
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
- select cells matching a material number
- if the selection's column 2 is not blank, add a row
- Add data in columns 2 & 3 from the form
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.
