连接目标列中的值 [英] Concatenating values in target column

问题描述

我有一个VBA代码的麻烦：有一个Excel表格（ Sheet1 ），其中包含两个必要的列（最后一个名字）

我想要做的是，每当你添加另一个最后一个列表中的第一个名称，它们都自动连接在另一个工作表中并形成一个新的列表（该列表的起始位置为 Sheet11.Range（AB3），on位置 AB2 是列表标题客户）。

因此，我的代码已输入 Sheet1 ：

  Private Sub Worksheet_Change（ByVal Target As Range）
 
 Dim tmp As Range 
对于Sheet1.Range（C4：C100）中的每个tmp 
如果tmp.Value<> 和tmp.Offset（0，1）.Value<> 然后
 Sheet11.Cells（Cells（Rows.Count，AB）。End（xlUp）.Row + 1，AB）。Value = tmp.Value& & tmp.Offset（0，1）.Value 
 End If 
 Next tmp 
 
 End Sub 
  

不幸的是，一进入第一个&该代码处于活动状态时，连接的名称不会一个接一个地列出，但列表中的姓氏将替换 AB2 中的列表标题。

我猜这个问题在循环过程的某个地方，但我可以似乎找出了它背后的逻辑。我会感谢任何建议来解决这个问题！

解决方案

我建议一个更快的更改事件 - 你不需要为每个更新循环所有行

这将添加新条目并更新现有的条目：

  Private Sub Worksheet_Change（ByVal Target As Range）
目标
如果.CountLarge = 1 And .Row> = 3 And（.Column = 3 Or .Column = 4）然后
 Dim cel As Range 
设置cel = Cells（.Row，3）
如果Len（cel）> 0和Len（cel.Offset（0,1））> 0然后
工作表（Sheet11）。范围（AB& .Row）= cel& & cel.Offset（0，1）
 End If 
 End If 
 End With 
 End Sub 
  

I'm having troubles with a VBA code: There's an Excel sheet (Sheet1) that contains two essential columns (last & first name)

What I am trying to do is, that whenever you add another last and first name to the list, both of them automatically get concatenated in another sheet and form a new list (start position for that list is Sheet11.Range("AB3"), on position AB2 is the list title "Clients").

My code therefore was entered in Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim tmp As Range
For Each tmp In Sheet1.Range("C4:C100")
If tmp.Value <> "" And tmp.Offset(0, 1).Value <> "" Then
Sheet11.Cells(Cells(Rows.Count, "AB").End(xlUp).Row + 1, "AB").Value = tmp.Value & " " & tmp.Offset(0, 1).Value
End If
Next tmp

End Sub

Unfortunately, as soon as I enter first & last names while this code is active, the concatenated names are not listed one after another, but the last name in the list replaces the list title in AB2.

I guess the problem lies somewhere within the loop process, but I can't seem to figure out the logic behind it. I'd be thankful for any suggestions to solve that problem!

解决方案

I suggest a faster Change event - you don't need to loop over all rows for every update

This will add new entries and update existing ones:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .CountLarge = 1 And .Row >= 3 And (.Column = 3 Or .Column = 4) Then
            Dim cel As Range
            Set cel = Cells(.Row, 3)
            If Len(cel) > 0 And Len(cel.Offset(0, 1)) > 0 Then
                Worksheets("Sheet11").Range("AB" & .Row) = cel & " " & cel.Offset(0, 1)
            End If
        End If
    End With
End Sub

这篇关于连接目标列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！