哪位大虾能帮帮我:如何在EXCEL表里实现校验?

yiluo

本人是菜鸟一个！



因为工作关系，本人经常要在EXCEL表格里输入集装箱号，而集装箱号是有一定校验规则的，如果箱号输入错误，会导致很多麻烦，因此，本人希望能在EXCEL表中的某一列实现箱号自动校验，如果箱号输入错误，可自动提示。为此，本人在网上搜索了一个箱号校验的VB代码，但小弟却不知如何修改一下。，加载到EXCEL里去。



希望哪为大虾仗义执言，小弟不胜感激~！







附代码如下：



Function ISO_Checking(Container_Num As String, Optional output_option As Integer) As String



'**********************

'* Written by Russell *

'*   PONL Wellington  *

'**********************



' OUTPUT OPTIONS:

' 0 = Check Digit as output

' 1 = Full Container Number with calculated check Digit as output

' 2 = "OK" or "Non ISO Number"



' check digit optional for options 0 & 1 but full container number required for option 2.



' Note: Incorrect format eg space between prefix and numbers will cause error handler to end

' Function and return "Wrong Format"



Dim FirstChar As String

Dim SecondChar As String

Dim ThirdChar As String

Dim ForthChar As String

Dim FifthChar As Byte

Dim SixthChar As Byte

Dim SeventhChar As Byte

Dim EighthChar As Byte

Dim NinthChar As Byte

Dim TenthChar As Byte

Dim EleventhChar As Byte

Dim First_Convert As Byte

Dim Second_Convert As Byte

Dim Third_Convert As Byte

Dim Forth_Convert As Byte

Dim Added_Value As Currency

Dim Check_Digit As Byte



On Error GoTo errorhandler





    FirstChar = UCase(Left(Container_Num, 1))       ' Split up container number to Characters

    SecondChar = UCase(Mid(Container_Num, 2, 1))

    ThirdChar = UCase(Mid(Container_Num, 3, 1))

    ForthChar = UCase(Mid(Container_Num, 4, 1))

    FifthChar = Mid(Container_Num, 5, 1)

    SixthChar = Mid(Container_Num, 6, 1)

    SeventhChar = Mid(Container_Num, 7, 1)

    EighthChar = Mid(Container_Num, 8, 1)

    NinthChar = Mid(Container_Num, 9, 1)

    TenthChar = Mid(Container_Num, 10, 1)

    EleventhChar = Right(Container_Num, 1)





Select Case FirstChar                      ' Convert first character of prefix to a number

    Case Is = "A"

        First_Convert = 10

    Case Is = "B"

        First_Convert = 12

    Case Is = "C"

        First_Convert = 13

    Case Is = "D"

        First_Convert = 14

    Case Is = "E"

        First_Convert = 15

    Case Is = "F"

        First_Convert = 16

    Case Is = "G"

        First_Convert = 17

    Case Is = "H"

        First_Convert = 18

    Case Is = "I"

        First_Convert = 19

    Case Is = "J"

        First_Convert = 20

    Case Is = "K"

        First_Convert = 21

    Case Is = "L"

        First_Convert = 23

    Case Is = "M"

        First_Convert = 24

    Case Is = "N"

        First_Convert = 25

    Case Is = "O"

        First_Convert = 26

    Case Is = ""

        First_Convert = 27

    Case Is = "Q"

        First_Convert = 28

    Case Is = "R"

        First_Convert = 29

    Case Is = "S"

        First_Convert = 30

    Case Is = "T"

        First_Convert = 31

    Case Is = "U"

        First_Convert = 32

    Case Is = "V"

        First_Convert = 34

    Case Is = "W"

        First_Convert = 35

    Case Is = "X"

        First_Convert = 36

    Case Is = "Y"

        First_Convert = 37

    Case Is = "Z"

        First_Convert = 38

End Select

     

Select Case SecondChar                        ' Convert second character of prefix to a number

    Case Is = "A"

        Second_Convert = 10

    Case Is = "B"

        Second_Convert = 12

    Case Is = "C"

        Second_Convert = 13

    Case Is = "D"

        Second_Convert = 14

    Case Is = "E"

        Second_Convert = 15

    Case Is = "F"

        Second_Convert = 16

    Case Is = "G"

        Second_Convert = 17

    Case Is = "H"

        Second_Convert = 18

    Case Is = "I"

        Second_Convert = 19

    Ca

yiluo

集装箱号由公司代码和7位数字组成，其中第七位数字就是校验码，



校验规则如下：



1、首先将公司代码转换为数字：



A=10 B=12 C=13 D=14 E=15F=16 G=17 H=18 I=19 J=20 K=21 L=23 M=24 N=25 O=26



P=27 Q=28 R=29 S=30 T=31 U=32 V=34 W=35 X=36 Y=37 Z=38



（去掉了11及倍数的数字）



例如：COSU800121 （C对应13 O对应26 S对应30 U对应32）



转换为：13/26/30/32/8/0/0/1/2/1



2、做13X1+26X2+30X4+32X8+8X16+0X32+0X64+1X128+2X256+1X512=1721



3、1721除以11，等于156余数为5，则校验码为5。



整个箱号为COSU8001215，如果输入正确，则进行其他操作，如果手误，输入如COSU8001211或2（最后一个数字）等等，则箱号肯定是错误的，则在EXCEL里有提示：“箱号输入错误！请重新输入！”，那就好了。

本人对VBA实在是一无所知!











希望大虾指点的是





1、代码优化，提示成中文，比如：“箱号错误！请重新输入！”





2、实现整个过程的步骤。





谢谢~~~~~~~！！！！！！！！！！！！

HeyEric

呵呵，这个并不难吧，就简单的字符串处理啊

红池坝

修改如下，将下面的代码复制粘贴到VBA编辑器的模块中即可：Public Function IDCheck(x As String) As StringDim ma, ma1, i As Integer

Dim Num(3) As Byte

Dim FifthChar As Byte

Dim SixthChar As Byte

Dim SeventhChar As Byte

Dim EighthChar As Byte

Dim NinthChar As Byte

Dim TenthChar As ByteIf Len(x) <> 11 Then       '输入数据不是11位

IDCheck = "位数错误"

Exit Function

End Ifma1 = Right(x, 1)For i = 1 To 4

   Num(i - 1) = Char_Num(UCase(Mid(x, i, 1)))

   If Num(i - 1) = 0 Then                                     '如果是错误代码（比如把字母输成了数字）

     IDCheck = "错误"

     Exit Function

   End If

Next    FifthChar = Mid(x, 5, 1)

    SixthChar = Mid(x, 6, 1)

    SeventhChar = Mid(x, 7, 1)

    EighthChar = Mid(x, 8, 1)

    NinthChar = Mid(x, 9, 1)

    TenthChar = Mid(x, 10, 1)ma = (Num(0) + Num(1) * 2 + Num(2) * 4 + Num(3) * 8 + FifthChar * 16 + SixthChar * 32 + SeventhChar * 64 + EighthChar * 128 + NinthChar * 256 + TenthChar * 512) Mod 11If Str(ma) = Str(ma1) Then

IDCheck = "正确"

Else

IDCheck = "校验错误"

End IfEnd Function'＝＝＝这是字母转换成数字的函数＝＝＝Public Function Char_Num(s As String) As Byte

Select Case s

    Case Is = "A"

        Char_Num = 10

    Case Is = "B"

        Char_Num = 12

    Case Is = "C"

        Char_Num = 13

    Case Is = "D"

        Char_Num = 14

    Case Is = "E"

        Char_Num = 15

    Case Is = "F"

        Char_Num = 16

    Case Is = "G"

        Char_Num = 17

    Case Is = "H"

        Char_Num = 18

    Case Is = "I"

        Char_Num = 19

    Case Is = "J"

        Char_Num = 20

    Case Is = "K"

        Char_Num = 21

    Case Is = "L"

        Char_Num = 23

    Case Is = "M"

        Char_Num = 24

    Case Is = "N"

        Char_Num = 25

    Case Is = "O"

        Char_Num = 26

    Case Is = ""

        Char_Num = 27

    Case Is = "Q"

        Char_Num = 28

    Case Is = "R"

        Char_Num = 29

    Case Is = "S"

        Char_Num = 30

    Case Is = "T"

        Char_Num = 31

    Case Is = "U"

        Char_Num = 32

    Case Is = "V"

        Char_Num = 34

    Case Is = "W"

        Char_Num = 35

    Case Is = "X"

        Char_Num = 36

    Case Is = "Y"

        Char_Num = 37

    Case Is = "Z"

        Char_Num = 38

    Case Else

        Char_Num = 0        '错误代码为0

    End Select

   

End Function

红池坝

使用方法：＝IDCheck(A1)