- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
45#
發表於 2013-10-15 15:58
| 只看該作者
回復 44# owen9399 - Private Function 資料檢查() As Boolean
- Dim s As String, E As Range, I As Integer, ii
- With Sh
- For I = 2 To UBound(ar)
- ii = 10 - Len(Sh.Cells(1, I))
- If I = 2 Or I = 3 Or I = 6 Then
- If ar(I).ListIndex = -1 Then s = s & IIf(s = "", "", vbLf) & Sh.Cells(1, I) + Space(ii) & vbTab & ar(I)
- Else
- If Not IsNumeric(ar(I)) And ar(I) <> "" Then s = s & IIf(s = "", "", vbLf) & Sh.Cells(1, I) + Space(ii) & vbTab & ar(I)
- End If
- Next
- If s <> "" Then
- 資料檢查 = True: MsgBox s, , "資料有誤!!": Exit Function
- ElseIf s = "" And ar(4) & ar(5) & ar(7) & ar(8) = "" Then
- 資料檢查 = True: MsgBox "出貨 進貨 沒有數量", , "資料有誤!!": Exit Function
- End If
- '******** 以下為檢查是否有相同的資料 如不需要可刪除 ****************
- s = "," & Join(ar, "")
- s = Replace(s, "," & ar(1), "") 'S: 結合控制項的字串 [ 序號公司產品名稱台北出貨1台北出貨2業務員進貨數量1進貨數量2 ]
- For Each E In .Range("B1", .Range("B1").End(xlDown)).Resize(, 7).Rows
- If s = Join(Application.Transpose(Application.Transpose(E.Value)), "") Then
- MsgBox Replace(Join(ar, ","), ar(1) & ",", "") & vbLf & "已存在為 第" & E.Row - 1 & " 筆 資料不可新增"
- 資料檢查 = True
- Exit Function
- End If
- Next
- '******************************************************************************
- End With
- End Function
|
|
發表於 2013-9-10 17:22
| 
