Excel 在同一单元镐入15位号码手动升为18位

技蕋>精华

TA的精华?a>

TA的得分?a>

发表于 2011-10-3 22:08|显示全部楼层

★财吾计、人力资源、行政、生管、销售、市场：Excel 行业应用系列视频课程精采放送中★

Function SFZ(号码)

On Error GoTo AA:

Dim S1 As String, S2 As String

Dim jym, X As Long

Dim NewId As String

Dim i As Long

Dim SID As String

SID = CStr(号码)

S1 = " 7 910 5 8 4 2 1 6 3 7 910 5 8 4 2"

S2 = "10X98765432"

If Len(SID) <> 15 And Len(SID) <> 18 Then

SFZ = "位数错蝟t;'测试的位数信息

ElseIf Len(SID) = 15 And Val(Mid(SID, 7, 2)) < 10 Then

SFZ = "年龄过磓uot;

ElseIf Len(SID) = 15 And Val(Mid(SID, 9, 2)) > 12 Then

SFZ = "陈份错蝢uot;

ElseIf Len(SID) = 15 And Val(Mid(SID, 11, 2)) > 31 Then

SFZ = "痴期错蝢uot;

ElseIf Len(SID) = 15 Then

NewId = Left(SID, 6) + "19" + Right(SID, 9)

jym = 0

For i = 1 To 17

jym = jym + Val(Mid(S1, i * 2 - 1, 2)) * Val(Mid(NewId, i, 1))

Next i

SFZ = NewId + Mid(S2, jym Mod 11 + 1, 1)

'测试18位的信息

ElseIf Len(SID) = 18 And Val(Mid(SID, 7, 2)) < 19 Then

SFZ = "酬错蝢uot;

ElseIf Len(SID) = 18 And Val(Mid(SID, 7, 4)) < Year(Date) - 65 Then

SFZ = "年龄少于65岁"

ElseIf Len(SID) = 18 And Val(Mid(SID, 7, 4)) > Year(Date) - 18 Then

SFZ = "年龄大于18岁"

ElseIf Len(SID) = 18 And Val(Mid(SID, 11, 2)) > 12 Then

SFZ = "陈份错蝢uot;

ElseIf Len(SID) = 18 And Val(Mid(SID, 13, 2)) > 31 Then

SFZ = "痴期错蝢uot;

Else

NewId = Left(SID, 17)

jym = 0

For i = 1 To 17

jym = jym + Val(Mid(S1, i * 2 - 1, 2)) * Val(Mid(NewId, i, 1))

Next i

If Mid(S2, jym Mod 11 + 1, 1) <> Mid(SID, 18, 1) Then

' SFZ = "识表，应为：" & Mid(S2, jym Mod 11 + 1, 1) _

' & "。" & Chr(13) & "已手动改正识保"

SFZ = Mid(SID, 1, 17) & Mid(S2, jym Mod 11 + 1, 1)

Else

SFZ = "正常"

End If

End If

GoTo AA:

AA:

If Err.Number <> 0 Then SFZ = Err.Description

End Function

复制代码

heike2004

heike2004当前离线

性眛>

保密

UID

阅读权限

注册时间

2010-5-11

时间

小时

最杭

1970-1-1

积分

财富

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/tongxinshuyu/article-134996-1.html