Excel VBA - 在表中选择，获取和设置数据 [英] Excel VBA - select, get and set data in Table

问题描述

我有一个工作表中有很多表，我刚刚开始使用表，因为它们似乎很方便。但是我从来没有在Excel表中操纵过内容。这些表基本上是列出了Firstname和Lastname的列。根据这些列的值，我想生成一个用户名。但是我正在尝试编写一个通用的Sub，它接受参数，例如工作表和表的名称。

以前，当数据没有在表格中时，我已经做到了：

 code>单元格（2，2）。选择
 
 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）。选择
循环直到IsEmpty（ActiveCell）
  

现在我试图做同样的事情，只有数据从表。有任何想法吗？我已经添加了一个ActiveSheet手表，看看我是否可以找到这些表，并且它们似乎位于 ActiveSheet.ListObjects 中，但是看不到任何。选择选项。也许我不需要选择表来操纵它的内容？

解决方案

当循环一个范围一个表或一个范围）通常更快地将数据复制到变体数组，操作该数组，然后将结果复制回表。

  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）。索引
 colUser = oUsers.ListColumns（UserName）。索引
 
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 ，colFirst），1）& v（i，colLast））
 Next 
 oUsers.ListColumns（UserName）DataBodyRange = vUserName 
 
 
 End Sub 
  

如果你真的要循环遍历本身：

 对于i = 1对于oUsers.ListRows.Count 
 oUsers.ListColumns（UserName）DataBodyRange.Rows（i）= LCase（Left（_ 
 oUsers.ListColumns（FirstName）。DataBodyRange.Rows（i） ，1）& _ 
 oUsers.ListColumns（LastName）DataBodyRange.Rows（i））
下一个
  

对于这种情况，您还可以使用 UserName 列中的公式，不需要vba

  = LOWER（LEFT（[@ FirstName]，1）& [@ LastName]）
  

编辑

对不起，不知道公式方式删除任何一个字符串中的字符您可能需要恢复到vba。这是一个用户定义的功能来做到这一点。您的公式将成为

  = DeleteChars（[@ UserName]，{$，＃}）
  

要删除字符替换 {$，＃ } 具有要删除的字符数组列表（只要您需要，您可以使列表）

替换字符使用 {$，＃;X，X} 是老字，后;新的。只要确保列表a的长度相同。

UDF代码：

 功能DeleteChars（r1 As Range，ParamArray c（）As Variant）As Variant 
 Dim i As Long 
 Dim s As String 
 
s = r1 
如果UBound（c（0），1）= 1然后
对于i = LBound（c（0），2）到UBound（c（0），2）
s =替换（s，c（0）（1，i），）
下一个
 Else 
对于i = LBound（c（0），2）到UBound（c（0），2）
s =替换（s，c（0） ，i），c（0）（2，i））
下一个
结束如果
 DeleteChars = s 
结束函数
  / pre> 
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?
解决方案
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


                        
这篇关于Excel VBA  - 在表中选择，获取和设置数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！