- 帖子
- 522
- 主題
- 36
- 精華
- 1
- 積分
- 603
- 點名
- 0
- 作業系統
- win xp sp3
- 軟體版本
- Office 2003
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2012-12-13
- 最後登錄
- 2021-7-11
|
本帖最後由 yen956 於 2014-3-11 14:44 編輯
公式太難了, 只好用VBA 試試看:
 - Option Explicit
- Private Sub CommandButton1_Click()
- Dim start1, end1, 空白列 As Long, Rng, findcell As Range
- Dim cour As Integer
- start1 = 1
-
- '[A65536].End(xlUp)→由下往上找, 直到找到非空白格為止
- end1 = [A65536].End(xlUp).Row
- Do
- Set Rng = Cells(start1, 1).Resize(end1 - start1 + 1, 1)
- Set findcell = Rng.Find(What:="name", _
- After:=Cells(start1, 1), _
- LookIn:=xlValues, _
- LookAt:=xlPart).Offset(1, 0)
-
- If Not findcell Is Nothing Then
- 空白列 = [i65536].End(xlUp).Row + 1
- Cells(空白列, 9) = findcell.Offset(-1, 1)
- Do
- '假定最多只有9科才成立
- cour = Val(Mid(findcell, 7, 1))
- Cells(空白列, 9).Offset(0, cour * 2 - 1) = findcell.Offset(0, 1)
- Cells(空白列, 9).Offset(0, cour * 2) = findcell.Offset(0, 4)
- Set findcell = findcell.Offset(1, 0)
- Loop Until findcell = ""
- End If
- start1 = findcell.Row
- Loop Until findcell Is Nothing Or start1 > end1
- End Sub
|
|
發表於 2014-3-11 14:43
| 
