我正在尝试连接给定行(例如C3:F3)中单元格的值,并在同一行中(在同一工作表上)转到已连接的单元格左侧的第一个空单元格(例如B3),然后输入连接的值。下面的代码是第一次工作,但是当运行下面的代码行时ws.Range("B3").End(xlDown).Offset(1, 0).Value = varConctnt
,即不断出现运行时错误1004 ,即,选择了下一行中的下一组单元格(例如C4:F4),我想要在单元格B4中输入连接值。我已尽力声明对象来解决此问题,但错误不断出现。
提前致谢。
Sub ConcatenateReal2()
Dim rng As Range, iRow As Integer, iCol As Integer, i As Integer
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Range("C3").Select
Set rng = ActiveSheet.Range(ActiveCell.End(xlToRight), ActiveCell.End(xlDown))
Dim varConctnt As Variant
For iRow = 1 To rng.Rows.Count
For iCol = 1 To rng.Columns.Count
If Not rng(iRow, iCol).Value = vbNullString Then
varConctnt = varConctnt & "," & rng(iRow, iCol).Value
End If
Next iCol
Range("B3").Activate
If IsEmpty(ActiveCell) Then
ActiveCell.Value = varConctnt
Else
ws.Range("B3").End(xlDown).Offset(1, 0).Value = varConctnt
End If
varConctnt = ""
skip1:
Next iRow
End Sub
(未经测试)
Sub ConcatenateReal2()
Dim rng As Range, c As Range
Dim sep, rw as Range, v, s
With ActiveSheet.Range("C3")
Set rng = .Parent.Range(.End(xlToRight), .End(xlDown))
End With
For Each rw in rng.Rows 'loop over rows
sep = "" 'reset separator
s = ""
For Each c in rw.Cells
v = c.value
If Len(v) > 0 Then
s = s & sep & v
sep = ","
end if
next c
rw.cells(1).offset(0, -1).value = s
Next rw
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句