一个用来将Excel中同一个人的多行记录中的特定列合并到第一行的宏

宏本身很简单,不过处理带链接的单元格,费了不少劲,主要是原来有个陋习,取单元格的值时,比较懒,没用cells(i,j).value,而是直接用cells(i,j),所以给添加链接时,因为最后一个参数使用了Cells(i,j),而不是Cells(i,j).value,导致类型不匹配报错.

Sub MergeRow()
Dim i, j, k As Integer
    Dim iStart As Integer
    iStart = 2
    For i = 3 To 65536
        If Cells(i, 3).Value = "" Then Exit For
        If Cells(i, 3).Value <> Cells(iStart, 3).Value Then
            iStart = i
        Else
            Cells(iStart, 55).Value = Cells(iStart, 55).Value + ";" + Cells(i, 55).Value
            Cells(iStart, 54).Value = Cells(iStart, 54).Value + ";" + Cells(i, 54).Value
            Cells(iStart, 50).Value = Cells(iStart, 50).Value + ";" + Cells(i, 50).Value
            For k = 57 To 100
                If Cells(i, k) <> "" Then
                    For j = 57 To 100
                        If Cells(iStart, j) = "" Then
                            Cells(iStart, j).Hyperlinks.Add Cells(iStart, j), Cells(i, k).Hyperlinks(1).Address, "", "", Cells(i, k).Value
                            Exit For
                        End If
                    Next j
                Else
                    Exit For
                End If
            Next k
        End If
    Next i
    MsgBox "处理完毕"
End Sub

转载于:https://www.cnblogs.com/techrich/archive/2010/12/07/1898900.html