网页资讯视频图片知道文库贴吧地图采购
进入贴吧全吧搜索
excel吧 关注：281,356贴子：1,549,807
  • 17回复贴，共1页

求助!求解释!求改写!

' Reformats a list from a simple delimitation to a numbered list ' Accepts arrays of strings for inList (allowing array formulas) ' numFormat is a standard Excel-style format string (default "0. ") ' inDelimiter is the delimiter in the input list ' outDelimiter is the delimiter for the output list Public Function TO_NUMBERED_LIST(inList As Variant, Optional numFormat As Variant, _ Optional inDelimiter As Variant, Optional outDelimiter As Variant) As Variant Dim i As Integer, j As Integer ' Set default parameters If IsMissing(numFormat) Then numFormat = "0). " If IsMissing(inDelimiter) Then inDelimiter = vbNewLine If IsMissing(outDelimiter) Then outDelimiter = inDelimiter If IsArray(inList) Then ' Must loop through each entry if using as an array formula Dim outList() As Variant ReDim outList(0 To (UBound(inList) - LBound(inList)), 1 To 1) j = 0 For i = LBound(inList) To UBound(inList) If IsError(inList(i, 1)) Then outList(j, 1) = inList(i, 1) Else outList(j, 1) = MakeNumbered(CStr(inList(i, 1)), CStr(numFormat), CStr(inDelimiter), CStr(outDelimiter)) End If j = j + 1 Next TO_NUMBERED_LIST = outList Else TO_NUMBERED_LIST = MakeNumbered(CStr(inList), CStr(numFormat), CStr(inDelimiter), CStr(outDelimiter)) End If End Function ' Helper function to do the actual work of splitting lists, numbering them, and recombining them Private Function MakeNumbered(inList As String, Optional numFormat As String, _ Optional inDelimiter As String, Optional outDelimiter As String) As String Dim i As Integer Dim tokenArr() As String tokenArr = Split(inList, inDelimiter) For i = 0 To UBound(tokenArr) tokenArr(i) = Format(i + 1, numFormat) & tokenArr(i) Next MakeNumbered = Join(tokenArr, outDelimiter) End Function


' Reformats a list from a simple delimitation to a numbered list
' Accepts arrays of strings for inList (allowing array formulas)
' numFormat is a standard Excel-style format string (default "0. ")
' inDelimiter is the delimiter in the input list
' outDelimiter is the delimiter for the output list
Public Function TO_NUMBERED_LIST(inList As Variant, Optional numFormat As Variant, _
Optional inDelimiter As Variant, Optional outDelimiter As Variant) As Variant
Dim i As Integer, j As Integer
' Set default parameters
If IsMissing(numFormat) Then numFormat = "0). "
If IsMissing(inDelimiter) Then inDelimiter = vbNewLine
If IsMissing(outDelimiter) Then outDelimiter = inDelimiter
If IsArray(inList) Then ' Must loop through each entry if using as an array formula
Dim outList() As Variant
ReDim outList(0 To (UBound(inList) - LBound(inList)), 1 To 1)
j = 0
For i = LBound(inList) To UBound(inList)
If IsError(inList(i, 1)) Then
outList(j, 1) = inList(i, 1)
Else
outList(j, 1) = MakeNumbered(CStr(inList(i, 1)), CStr(numFormat), CStr(inDelimiter), CStr(outDelimiter))
End If
j = j + 1
Next
TO_NUMBERED_LIST = outList
Else
TO_NUMBERED_LIST = MakeNumbered(CStr(inList), CStr(numFormat), CStr(inDelimiter), CStr(outDelimiter))
End If
End Function
' Helper function to do the actual work of splitting lists, numbering them, and recombining them
Private Function MakeNumbered(inList As String, Optional numFormat As String, _
Optional inDelimiter As String, Optional outDelimiter As String) As String
Dim i As Integer
Dim tokenArr() As String
tokenArr = Split(inList, inDelimiter)
For i = 0 To UBound(tokenArr)
tokenArr(i) = Format(i + 1, numFormat) & tokenArr(i)
Next
MakeNumbered = Join(tokenArr, outDelimiter)
End Function


2025-07-20 01:08:25
广告
这是从网络上找的一段代码


我的基本诉求是:
单元格内插入换行符,自动换行后,对换行后的数据进行自动编码序号.
应该描述清楚了.


那段代码应该是很早起的自定义函数应用.


我看不懂,
麻烦路过的大神指导一下.
帮忙解释一下
帮忙删改无用信息,保留完整有用信息.
连接符"-"或"+"


Option Explicit
' Reformats a list from a simple delimitation to a numbered list
' Accepts arrays of strings for inList (allowing array formulas)
' numFormat is a standard Excel-style format string (default "0. ")
' inDelimiter is the delimiter in the input list
' outDelimiter is the delimiter for the output list
Public Function TO_NUMBERED_LIST(inList As Variant, Optional numFormat As Variant, _
　Optional inDelimiter As Variant, Optional outDelimiter As Variant) As Variant
　Dim i As Integer, j As Integer
' Set default parameters If IsMissing(numFormat) Then numFormat = "0). "
　If IsMissing(inDelimiter) Then inDelimiter = vbNewLine
　If IsMissing(outDelimiter) Then outDelimiter = inDelimiter
　If IsArray(inList) Then
　　' Must loop through each entry if using as an array formula
　　Dim outList() As Variant
　　ReDim outList(0 To (UBound(inList) - LBound(inList)), 1 To 1)
　　j = 0
　　For i = LBound(inList) To UBound(inList)
　　　If IsError(inList(i, 1)) Then
　　　　outList(j, 1) = inList(i, 1)
　　　Else
　　　　outList(j, 1) = MakeNumbered(CStr(inList(i, 1)), CStr(numFormat), CStr(inDelimiter), CStr(outDelimiter))
　　　End If
　　　j = j + 1
　　Next
　　TO_NUMBERED_LIST = outList
　Else
　　TO_NUMBERED_LIST = MakeNumbered(CStr(inList), CStr(numFormat), CStr(inDelimiter), CStr(outDelimiter))
　End If
End Function
' Helper function to do the actual work of splitting lists, numbering them, and recombining them
Private Function MakeNumbered(inList As String, Optional numFormat As String, _
　Optional inDelimiter As String, Optional outDelimiter As String) As String
　Dim i As Integer
　Dim tokenArr() As String
　tokenArr = Split(inList, inDelimiter)
　For i = 0 To UBound(tokenArr)
　　tokenArr(i) = Format(i + 1, numFormat) & tokenArr(i)
　Next
　MakeNumbered = Join(tokenArr, outDelimiter)
End Function






2025-07-20 01:02:25
广告


Option Explicit
Sub abc()
　Dim r As Range, i, j, t
　For Each r In ActiveSheet.UsedRange
　　If Len(r.Value) Then
　　　If Left(r.Value, 2) <> "1." Then
　　　　t = Split(Replace(r.Value, vbLf, "+"), "+")
　　　　For j = 0 To UBound(t)
　　　　　t(j) = j + 1 & "." & t(j)
　　　　Next
　　　　r = Join(t, vbLf)
　　　End If
　　End If
　Next
End Sub


'加了几个限定条件
Option Explicit
Sub abc()
　Dim r As Range, i, j, t, n
　For Each r In ActiveSheet.UsedRange
　　If Len(r.Value) Then
　　　If Left(r.Value, 2) <> "1." Then
　　　　t = Replace(r.Value, vbLf, "+")
　　　　If InStr(t, "+") Then
　　　　　t = Split(t, "+"): n = 0
　　　　　For j = 0 To UBound(t)
　　　　　　If Len(t(j)) Then t(j) = j + 1 & "." & t(j): n = n + 1
　　　　　Next
　　　　　If n > 1 Then ReDim Preserve t(n - 1): r = Join(t, vbLf)
　　　　End If
　　　End If
　　End If
　Next
End Sub


扫二维码下载贴吧客户端

下载贴吧APP
看高清直播、视频！
  • 17回复贴，共1页
分享到: