'上帖附件答案,等我做起,但要255的权限查看
Option Explicit
Sub 太耗电了() '改一处为 立本的商务核心
Dim ar, i As Long, j As Long, k As Long, Ran As Range, Cel As Range, r As Long, s As String
ar = Sheet2.Range("A1").CurrentRegion.Resize(10, 49)
ReDim br(1 To 8000, 1 To UBound(ar, 2))
For j = 2 To UBound(ar, 2)
For i = 2 To UBound(ar)
If Len(ar(i, j)) = 0 Then ar(i, j) = ar(i - 1, j)
Next
Next
For j = 2 To UBound(ar, 2)
ar(10, j) = Replace(ar(10, j), "~", "~~")
Next
With Sheet1
For i = 1 To .Range("B1000").End(xlUp).Row
s = .Cells(i, 2).Value
If Len(s) Then
k = k + 1
br(k, 1) = s
r = .Cells(i, 2).MergeArea.Rows.Count
Set Ran = .Rows(i).Resize(r)
For j = 2 To UBound(ar, 2)
Set Cel = Ran.Find(ar(10, j) & "*", , , , 2, 1 - (ar(10, j) = "地点"))
If Not Cel Is Nothing Then
If ar(10, j) = "客户跟进情况状态/情况" Then
br(k, j) = Cel.Offset(, 1).Value '此处与模拟不完全同,自己看
Else
s = Replace(Cel.Value, ar(10, j), "")
If InStr(s, ":") Then br(k, j) = Split(s, ":")(1) Else br(k, j) = s
End If
End If
Set Cel = Nothing
Next
Set Ran = Nothing
End If
Next
End With
Sheet2.Range("a16").Resize(k, UBound(br, 2)) = br
End Sub |