[Dba-office] Excet Table with VBA

[Susan Harkins]

How do you do that so fast? :)

Susan H.

On Fri, Dec 18, 2015 at 10:14 AM, Salakhetdinov Shamil <mcp2004 at mail.ru>
wrote:

>  Susan --
>
> Here is how you can specify a variable set of values to be assigned for a
> newly added row's cells:
>
> Public Sub aTest()
> testAddXlTableRow2 "v1", "v2", "v3"
> End Sub
> Public Function testAddXlTableRow2(ParamArray values() As Variant) As
> listRow
> ' Key column index is assumed to be 1
> Dim keyColumnIndex As Integer
> keyColumnIndex = 1
> Dim lst As ListObject
> Set lst = ThisWorkbook.Worksheets("DataValidation").ListObjects("Table1")
> Dim rowIndex As Integer
> For rowIndex = 1 To lst.ListRows.Count
> If lst.ListRows(rowIndex).Range(keyColumnIndex) = values(0) Then
> lst.ListRows(rowIndex).Range.Select
> MsgBox "Duplicate value found at row # = " + CStr(rowIndex)
> Set testAddXlTableRow2 = Nothing
> Exit Function
> End If
> Next rowIndex
> Dim listRow As listRow
> Dim valueIndex As Integer
> Set listRow = lst.ListRows.Add(AlwaysInsert:=True)
> For valueIndex = 1 To UBound(values) + 1
> listRow.Range.Cells(valueIndex) = values(valueIndex - 1)
> Next valueIndex
> Set testAddXlTableRow2 = listRow
> End Function
> -- Shamil
>
>
> >Friday, December 18, 2015 9:37 AM -05:00 from Susan Harkins <
> ssharkins at gmail.com>:
> >
> >Thanks Shamil -- it's similar to what I ended up with last night. Let's
> compare! I think the only real difference is selecting the newly inserted
> row. I knew going in that Tables are internally, a list object, but I had
> anticipated more Table properties and methods, but nope... :) There really
> should be an Add method that lets you specify (optional) new values for the
> new rows! I expected that, but didn't find it.
> >
> >I find Excel's object model confusing and I struggle to find the right
> data types and objects in VBA.
> >
> >Thank you for helping! -- Susan H.
> >
> >Private Sub CommandButton1_Click()
> >    'Enter value into ListPerson column in Table1.
> >
> >    Dim str As String
> >    Dim dbl As Double
> >    Dim rng As Range
> >    Dim oNewRow As ListRow
> >
> >    str = TextBox1.Value
> >
> >    Set rng =
> ThisWorkbook.Worksheets("DataValidation").Range("Table1[ListPeople]")
> >
> >    'Handle Match error when match not found.
> >
> >    If Not IsError(Application.Match(str, rng, 0)) Then
> >        dbl = Application.Match(str, rng, 0)
> >    Else
> >        dbl = 0
> >    End If
> >
> >    'If match is found, display duplicate error.
> >    'If match not found, add new record to Table1.
> >    If dbl <> 0 Then
> >        MsgBox "You can't enter duplicate values.", vbOKOnly, "Error"
> >        Exit Sub
> >    Else
> >       'Select Table1.
> >        rng.Select
> >        Set oNewRow =
> Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
> >        oNewRow.Range.Cells().Value = str
> >        Set oNewRow = Nothing
> >    End If
> >
> >End Sub
> >
> ><<< skipped >>>
> _______________________________________________
> Dba-office mailing list
> Dba-office at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-office
>