Excel VBA - select, get and set data in Table

Question

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?

chris neilsen · Accepted Answer · 2011-10-25 07:01:54Z

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

That last formula is perfect! Thank you! Do you btw have any way of combining that formula to replace characters? Some people have weird characters in their last names and I'd want to replace those for usernames. Only if you happen to know :)

Collectives™ on Stack Overflow

Excel VBA - select, get and set data in Table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related