我试图在Excel中连接一堆列。我知道我可以手动执行以下操作:
=A1&", "&B1&", "&C1
(以此类推)
但我有大约40个列,而且我正在寻找一种简化这个过程的方法。
提前感谢任何帮助!
我试图在Excel中连接一堆列。我知道我可以手动执行以下操作:
=A1&", "&B1&", "&C1
(以此类推)
但我有大约40个列,而且我正在寻找一种简化这个过程的方法。
提前感谢任何帮助!
range
参数的用户函数Public Function ClarkeyCat(ByRef rng As Range) As Variant
Dim c As Range
Dim ans As Variant
For Each c In rng
If (c.Value <> "") Then
ans = IIf(ans = "", "", ans & ",") & c.Value
End If
Next
ClarkeyCat = ans
End Function
如果你需要将Variant
类型更改为string
,可以按以下方式使用:
myString = ""
for i = 1 to 40
if i <> 40 then
myString = myString & Cells(1, i) & ", "
else:
myString = myString & Cells(1, i)
end if
next i
myString将包含您连接字符串的内容。
我也遇到过这个问题,让我也贴一下我的函数。
我的问题通常出现在我尝试连接日期、错误和空单元格时。
因此,我尝试使用以下方法来解决大部分问题:
Function CONCATPLUS(ref_value As Range, Optional delimiter As Variant) As String
Dim cel As Range
Dim refFormat As String, myvalue As String
If ref_value.Cells.Count = 1 Then CONCATPLUS = CVErr(xlErrNA): Exit Function
If IsMissing(delimiter) Then delimiter = " "
For Each cel In ref_value
refFormat = cel.NumberFormat
Select Case TypeName(cel.Value)
Case "Empty": myvalue = vbNullString
Case "Date": myvalue = Format(cel, refFormat)
Case "Double"
Select Case True
Case refFormat = "General": myvalue = cel
Case InStr(refFormat, "?/?") > 0: myvalue = cel.Text
Case Else: myvalue = Format(cel, refFormat)
End Select
Case "Error"
Select Case True
Case cel = CVErr(xlErrDiv0): myvalue = "#DIV/0!"
Case cel = CVErr(xlErrNA): myvalue = "#N/A"
Case cel = CVErr(xlErrName): myvalue = "#NAME?"
Case cel = CVErr(xlErrNull): myvalue = "#NULL!"
Case cel = CVErr(xlErrNum): myvalue = "#NUM!"
Case cel = CVErr(xlErrRef): myvalue = "#REF!"
Case cel = CVErr(xlErrValue): myvalue = "#VALUE!"
Case Else: myvalue = "#Error"
End Select
Case "Currency": myvalue = cel.Text
Case Else: myvalue = cel
End Select
If Len(myvalue) <> 0 Then
If CONCATPLUS = "" Then
CONCATPLUS = myvalue
Else
CONCATPLUS = CONCATPLUS & delimiter & myvalue
End If
End If
Next
End Function
目前为止,我还没有遇到过这个函数无法连接的单元格输入。
随意根据您的需求进行调整。希望对您有所帮助。
当连接单行或单列范围时,您可以使用Application.Transpose
一次完成操作,以避免范围循环。
这个UDF有三个参数:
,
)TRUE
表示范围-经过进一步思考,我将更新UDF以自动检测范围是基于row
还是column
)请注意,在其他答案方面:
IIF
评估TRUE
和FALSE
参数,因为VBA
不会[短路](http://en.wikipedia.org/wiki/Short-circuit_evaluation)。因此,在循环内部使用IFF
可能会很昂贵。代码
Function ConCat(rng1 As Range, Optional StrDelim As String, Optional bRow As Boolean) As String
Dim x
If StrDelim = vbNullString Then StrDelim = ","
x = Application.Transpose(rng1)
If bRow Then x = Application.Transpose(x)
ConCat = Join(x, StrDelim)
End Function
D1
)是 =concat(A1:C1,",",TRUE)
- E1
中的公式是 =concat(E3:E5,", ")
'The "&" must have a space on both sides or it will be
'treated as a variable type of long integer.
ActiveCell.Offset(0, 1).FormulaR1C1 = _
ActiveCell.Offset(0, -1) & " " & ActiveCell.Offset(0, 0)
ActiveCell.Offset(1, 0).Select
循环
结束子程序