|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Option Explicit
- Function SJTJTQ(rgSource As Range, lngStartRowID As Long) As Variant
- Dim arrSource As Variant, arrResult As Variant, lngRow As Long
- Dim rgCaller As Range, lngStartID As Long, lngMax As Long, lngEnd As Long
- Dim strContent As String, strSplit() As String, lngStep As Long
-
- Set rgCaller = Application.Caller
- lngMax = rgCaller.Rows.Count '公式所在的总行数
- ReDim arrResult(1 To lngMax, 1 To 1) As String
-
- If rgSource.Columns.Count > 1 Then
- arrResult(1, 1) = "数据区域只能是1列!"
- SJTJTQ = arrResult
- Exit Function
- End If
-
- If rgSource.Rows.Count > lngMax Then
- arrResult(1, 1) = "公式区域小于数据区域!"
- SJTJTQ = arrResult
- Exit Function
- End If
-
- If rgSource(1).Row <> rgCaller(1).Row Then
- arrResult(1, 1) = "公式与数据首行不对应!"
- SJTJTQ = arrResult
- Exit Function
- End If
-
- If lngStartRowID < 1 Then
- arrResult(1, 1) = "指定行号错误"
- SJTJTQ = arrResult
- Exit Function
- End If
- If lngStartRowID > 2 Then
- If lngStartRowID < rgSource(1).Row Or lngStartRowID > rgSource(1).Row + rgSource.Rows.Count - 1 Then
- arrResult(1, 1) = "指定行号错误"
- SJTJTQ = arrResult
- Exit Function
- End If
- End If
-
- Set rgCaller = Nothing
-
- '判断数据的起始、结束行号
- lngRow = rgSource(1).Row - 1
- If Trim(rgSource(1).Value) <> "" Then
- lngStartID = 1
- Else
- Set rgCaller = rgSource.Find("*", LookIn:=xlValues)
- If rgCaller Is Nothing Then
- arrResult(1, 1) = "无数据!"
- SJTJTQ = arrResult
- Exit Function
- End If
- lngStartID = rgSource.Find("*", LookIn:=xlValues).Row - lngRow
- End If
- lngEnd = rgSource.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row - lngRow
-
- arrSource = rgSource
- arrSource = Application.WorksheetFunction.Transpose(arrSource)
- strContent = Application.WorksheetFunction.Trim(Join(arrSource, Space(1)))
- strSplit = Split(strContent, Space(1))
-
- Select Case lngStartRowID
- Case 1 '顺排
- For lngRow = LBound(strSplit) To UBound(strSplit)
- arrResult(lngRow + lngStartID, 1) = strSplit(lngRow)
- Next
- Case 2 '逆排
- For lngRow = UBound(strSplit) To LBound(strSplit) Step -1
- arrResult(lngEnd, 1) = strSplit(lngRow)
- lngEnd = lngEnd - 1
- Next
- Case Else '指定行号逆排
- lngStartRowID = lngStartRowID - rgSource(1).Row + 1
- For lngRow = UBound(strSplit) To LBound(strSplit) Step -1
- arrResult(lngStartRowID, 1) = strSplit(lngRow)
- lngStartRowID = lngStartRowID - 1
- If lngStartRowID = 0 Then Exit For
- Next
- End Select
-
- SJTJTQ = arrResult
- End Function
复制代码 |
评分
-
1
查看全部评分
-
|