Q2

櫃號檢查-巨集一一檢查版

示範檔

範例檔

Q2.XLSX

結果檔

Q2F.XLSX

錦子老師,您好,

因工作需要,需常檢查貨櫃號碼有無key錯,但需自行一個一個copy&paste上去。但我想知道是否可以把它編寫成程式,放入Excel中呢?

檢查碼公式如下:

Ex.櫃號APHU4517446,其檢查碼為尾端的6

1.先將前英文字母部份換成值(英文字母代碼對照表如下表)。

10

12

13

14

15

16

17

18

19

20

21

23

24

25

26

27

28

29

 

30

31

32

34

35

36

37

38

 

2.位數之乘數

1

2

3

4

5

6

7

8

9

0

1

2

4

8

16

32

64

128

256

512

3.APHU英文部份換算成代碼為10,27,18,32

4.然後將四個英文+6個數字共後的6個數字。

(1碼的值*1+2碼的值*2+3碼的值*4+4碼的值*8+5碼的值*16+6碼的值*32+7碼的值*64+8碼的值*128+9碼的值*256+10碼的值*512+)除以11

所以10*1+27*2+18*4+32*8+4*16+5*32+1*64+7*128+4*256+4*152=4648

求出4648後,除11=442.545454

取小數點後0.545454*11=5.99999(A),再四捨五入到整數值得出6

6就是檢查碼,也就是櫃號尾碼。

(A)值大於或等於10,需再減10,則檢查碼則為01

錦子老師,若將櫃號(不含檢查碼)放於A欄,自動計算後之檢查碼自動放入B欄,可以這樣的嗎?

當看到上面這位讀者的來信,一堆堆運算條件真是恐佈,其實認真看完後覺得沒什麼,只是我們要將其分解成一些動作。

1.首先啟動Excel並開啟該活頁簿。

2.ALT+F11鍵啟動VBA視窗。

3.點取「插入 > 模組」指令,開啟一個新的模組。

4.在模組中輸入巨集內容,如下所示:

Sub TEU()

Dim S, T, U, X, Y, NO, TOTAL

A = InputBox("請輸入櫃號")

NO = Mid(A, 5, 6)

For T = 1 To 4

   S = UCase(Mid(A, T, 1))

   Select Case S

    Case "A"

        U = 10

    Case "B"

        U = 12

    Case "C"

        U = 13

    Case "D"

        U = 14

    Case "E"

        U = 15

    Case "F"

        U = 16

    Case "G"

        U = 17

    Case "H"

        U = 18

    Case "I"

        U = 19

    Case "J"

        U = 20

    Case "K"

        U = 21

    Case "L"

        U = 23

    Case "M"

        U = 24

    Case "N"

        U = 25

    Case "O"

        U = 26

    Case "P"

        U = 27

    Case "Q"

        U = 28

    Case "R"

        U = 29

    Case "S"

        U = 30

    Case "T"

        U = 31

    Case "U"

        U = 32

    Case "V"

        U = 34

    Case "W"

        U = 35

    Case "X"

        U = 36

    Case "Y"

        U = 37

    Case "Z"

        U = 38

   End Select

   If T = 1 Then

      X = U

   Else

      If T = 2 Then

         X = X + (U * 2)

      Else

         If T = 3 Then

            X = X + (U * 4)

         Else

            X = X + (U * 8)

         End If

      End If

   End If

Next

 

TOTAL = X + (Mid(NO, 1, 1) * 16) + (Mid(NO, 2, 1) * 32) + (Mid(NO, 3, 1) * 64) + (Mid(NO, 4, 1) * 128) + (Mid(NO, 5, 1) * 256) + (Mid(NO, 6, 1) * 512)

Y = Round(((TOTAL / 11) - Int(TOTAL / 11)) * 11, 0)

Z = CInt(Mid(A, 11, 1))

If Y <> Z Then

   MsgBox "您的櫃號有誤"

End If

End Sub

5.點取Excel視窗,再點取「檢視 > 巨集 > 巨集」下拉方塊,選擇「檢視巨集」指令,如下圖所示。

6.在「巨集名稱」列示方塊中點取「TEU」項目,再點取「執行」鈕,如下圖所示,開啟「Microsoft Excel」對話方塊。

7.輸入櫃號,如下圖所示。

8.點取「確定」鈕,若櫃號有問題會出現如下圖的對話方塊,若櫃號沒問題,則不會出現任何對話方塊。


arrow
arrow

    錦子老師 發表在 痞客邦 留言(0) 人氣()