Excel宏连接结果给出错误的最终结果

Question

我有这个宏连接所选单元格的值并将其放入输入框中。

我无法删除输入框中的结果括号之前的最后一个逗号。

Thanx的帮助！

Private Sub CommandButton2_Click() 
    Dim celCurrentCell As Range 
    Dim celFirstCell As Range 
    Dim i As Integer 
    Dim txtTempText As String 
    i = 0 
    txtTempText = "" 
    For Each celCurrentCell In Selection 
     If i = 0 Then 
      i = 1 
      Set celFirstCell = celCurrentCell 
     End If 
     txtTempText = txtTempText & celCurrentCell.Value & "','" 
    Next 
    txtTempText = Left(txtTempText, Len(txtTempText) - 1) 

    InputBox "Copier/coller le texte", "Concatenator", "('" + txtTempText + ")" 
End Sub 

Answer 1

Private Sub CommandButton2_Click() 

    Dim celCurrentCell As Range 
    Dim celFirstCell As Range 
    Dim i As Integer 
    Dim txtTempText As String, sep as string 

    i = 0 
    txtTempText = "" 
    For Each celCurrentCell In Selection 
     If i = 0 Then 
      i = 1 
      Set celFirstCell = celCurrentCell 
     End If 
     txtTempText = txtTempText & sep & celCurrentCell.Value 
     sep = "','" 
    Next 

    InputBox "Copier/coller le texte", "Concatenator", "('" + txtTempText + "')" 


End Sub 

Answer 2

假设你在在单个列的单个行或多个小区中选择多个小区，这消除循环。

Option Explicit 

Private Sub CommandButton2_Click() 
    Dim strTemp As String, sep As String 

    sep = "','" 
    If Selection.Rows.Count > Selection.Columns.Count Then 
     strTemp = "('" & Join(Application.Transpose(Selection.Columns(1).Value2), sep) & "')" 
    Else 
     strTemp = "('" & Join(Application.Transpose(Application.Transpose(Selection.Rows(1).Value2)), sep) & "')" 
    End If 
    Debug.Print strTemp 

    InputBox "Copier/coller le texte", "Concatenator", strTemp 

End Sub