- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 98
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2024-4-27
              
|
回復 7# lone_tiger0303 - Private Sub CommandButton1_Click()
- Dim Ay(3, 2), My As Range, A As Range
- Ay(0, 0) = 0: Ay(0, 1) = "忠誠客": Ay(1, 0) = 9.1: Ay(1, 1) = "久未回客": Ay(2, 0) = 12.1: Ay(2, 1) = "流失客"
- With Sheet1
- For Each A In .Range(.[A2], .[A65536].End(xlUp))
- Set My = .Columns("Y").Find(A, lookat:=xlWhole)
- If Not My Is Nothing Then A.Offset(, 4) = My.Offset(, 1)
- If A.Offset(, 18) < A.Offset(, 4) Then A.Offset(, 18) = ""
- If A.Offset(, 18) > A.Offset(, 4) And A.Offset(, 19) = "" Then A.Offset(, 19) = DateAdd("m", 3, A.Offset(, 18))
- If Date > A.Offset(, 19) Then A.Offset(, 18).Resize(, 2) = ""
- Next
- Set rng = .Range(.[E2], .[E65536].End(xlUp))
- For Each A In rng
- If IsDate(A) Then
- m = Application.Max(0, Round((Date - A) / 30, 2))
- k = Application.VLookup(m, Ay, 2)
- A.Offset(, 11).Resize(, 3) = Array(m, k, A.Offset(, -3) & "-" & k)
- End If
- Next
- End With
- End Sub
|
|
發表於 2010-5-24 15:47
| 
