CODEWITHSUNDEEP
vbaexcel
Kenny Bones
Kenny Bones

Reputation: 5139

Excel VBA - select, get and set data in Table

I've got a worksheet with a lot of tables in them and I'm just starting to use tables because they seem pretty handy. But I've never manipulated content in an Excel table before. And these tables are basically lists of columns with Firstname and Lastname. Based on the values on these columns, I want to generate a username. But I'm trying to write a generic Sub that takes arguments, such as worksheet and name of the table.

Previously I've done this when the data has not been in a table:

Cells(2, 2).Select

Do
    strFirstName = ActiveCell.Value
    strLastName = ActiveCell.Offset(0, 2).Value

    strFirstName = Left(strFirstName, 1)

    strUserName = strFirstName & strLastName
    strUserName = LCase(strUserName)

    ActiveCell.Offset(0, 5).Value = strUserName
    ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)

And now I'm trying to do the exact same thing, only with data from a Table. Any ideas? I've added a watch for "ActiveSheet" to see if I can find the tables, and they seem to be in ActiveSheet.ListObjects, but I couldn't see any .Select option there. Perhaps I don't need to select the Table in order to manipulate it's content?

Upvotes: 2

Views: 56393

Answers (1)

chris neilsen
chris neilsen

Reputation: 53166

When looping over a range (whether in a table or in a range) it is usually faster to copy the data to a variant array, manipulate that array, and then copy the result back to the sheet.

Sub zz()
    Dim oUsers As ListObject
    Dim v As Variant
    Dim vUserName() As Variant
    Dim i As Long
    Dim colFirst As Long
    Dim colLast As Long
    Dim colUser As Long

    Set oUsers = ActiveSheet.ListObjects(1)
    colFirst = oUsers.ListColumns("FirstName").Index
    colLast = oUsers.ListColumns("LastName").Index
    colUser = oUsers.ListColumns("UserName").Index

    v = oUsers.DataBodyRange
    ReDim vUserName(1 To UBound(v, 1), 1 To 1)
    For i = 1 To UBound(v, 1)
        vUserName(i, 1) = LCase(Left(v(i, colFirst), 1) & v(i, colLast))
    Next
    oUsers.ListColumns("UserName").DataBodyRange = vUserName


End Sub

If you really want to loop over the range itself:

    For i = 1 To oUsers.ListRows.Count
        oUsers.ListColumns("UserName").DataBodyRange.Rows(i) = LCase(Left( _
         oUsers.ListColumns("FirstName").DataBodyRange.Rows(i), 1) & _
         oUsers.ListColumns("LastName").DataBodyRange.Rows(i))
    Next

For this situation you could also just use a formula in the UserName column itself, with no vba required

=LOWER(LEFT([@FirstName],1)&[@LastName])

EDIT

Sorry, don't know of a Formula way to remove any of a list of characters from a string. You might have to revert to vba for this. Here's a user defined function to do it. Your formula will become

=DeleteChars([@UserName],{"$","#"})

To Delete the characters replace {"$","#"} with a array list of characters you want to remove (you can make the list as long as you need)
To replace the characters use {"$","#";"X","X"} where the list up to the ; is the old characters, after the ; the new. Just make sure the listsa are the same length.

UDF code:

Function DeleteChars(r1 As Range, ParamArray c() As Variant) As Variant
    Dim i As Long
    Dim s As String

    s = r1
    If UBound(c(0), 1) = 1 Then
        For i = LBound(c(0), 2) To UBound(c(0), 2)
            s = Replace(s, c(0)(1, i), "")
        Next
    Else
        For i = LBound(c(0), 2) To UBound(c(0), 2)
            s = Replace(s, c(0)(1, i), c(0)(2, i))
        Next
    End If
    DeleteChars = s
End Function

Upvotes: 6

Related Questions