- 帖子
- 12
- 主題
- 2
- 精華
- 0
- 積分
- 14
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Office 2013
- 閱讀權限
- 10
- 註冊時間
- 2013-11-25
- 最後登錄
- 2014-1-17
|
試試這個- Sub copydata()
- Dim s1 As Long, s2 As Long, s3 As Long, d1 As Long, d2 As Long
- Dim wages As Long, OT As Long
- i = 1
- While Sheets(1).Cells(i, 1) <> "薪金"
- i = i + 1
- Wend
- wages = i
- While Sheets(1).Cells(i, 1) <> "加班"
- i = i + 1
- Wend
- OT = i
- For i = wages To Sheets(1).UsedRange.Rows.Count
- s1 = Left(Sheets(1).Cells(i, 2), 4)
- s2 = Mid(Sheets(1).Cells(i, 2), 6, InStr(1, Sheets(1).Cells(i, 2), "月") - 5 - 1)
- If InStr(1, Sheets(1).Cells(i, 2), "-") <> 0 Then
- s3 = Mid(Sheets(1).Cells(i, 2), InStr(1, Sheets(1).Cells(i, 2), "-") + 1, Len(Sheets(1).Cells(i, 2)) - (InStr(1, Sheets(1).Cells(i, 2), "-") + 1))
- Else
- s3 = 0
- End If
- For j = 2 To Sheets(2).UsedRange.Rows.Count
- d1 = Left(Sheets(2).Cells(j, 1), 4)
- d2 = Mid(Sheets(2).Cells(j, 1), 6, InStr(1, Sheets(2).Cells(j, 1), "月") - 1 - 5)
-
- If d1 * 100 + d2 >= s1 * 100 + s2 Then
- If s3 <> 0 Then
- If d1 * 100 + d2 <= s1 * 100 + s3 Then
- If i >= OT Then
- Sheets(2).Cells(j, 3) = Sheets(1).Cells(i, 3)
- Else
- Sheets(2).Cells(j, 2) = Sheets(1).Cells(i, 3)
- End If
- End If
- Else
- If i >= OT Then
- Sheets(2).Cells(j, 3) = Sheets(1).Cells(i, 3)
- Else
- Sheets(2).Cells(j, 2) = Sheets(1).Cells(i, 3)
- End If
- End If
- End If
- Next j
- Next i
- End Sub
|
|
發表於 2013-11-25 01:45
| 