取订单号的程序

最新推荐文章于 2024-01-23 23:38:23 发布

原创最新推荐文章于 2024-01-23 23:38:23 发布 · 653 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excelvba编程实例

002 Excel VBA 专栏收录该内容

10 篇文章

订阅专栏

本文介绍了一段VBA代码，用于从Excel表格中提取特定格式的订单号，并去除非数字及顺序号部分，确保提取出的订单号符合规定格式。

Option Explicit

Sub 仓库获取客户代码()
    If Worksheets("产品入库序时簿").Range("G1").Value = "收货仓库" Then
        Columns("G:G").Select
        Selection.Insert
    End If

    getSONo "产品入库序时簿", "F", 2, "G", 2
End Sub
Private Function getSONo(xlWsh As String, strRangeIn As String, intRangeIn As Integer, strRangeOut As String, intRangeOut As Integer)
    '定义两个变量存储单元格Range位置编号
    Dim strStartRangeID As String
    Dim strOutputRangeID As String
    Dim strFinalSONo As String
    '定义一个整数变量，存编号到第几行为止
    Dim intStop As Integer
    intStop = ActiveWorkbook.Worksheets(xlWsh).UsedRange.Rows.Count

    '设置输出的那些单元格为文本格式
    ActiveWorkbook.Worksheets(xlWsh).Range(strRangeOut & ":" & strRangeOut).NumberFormatLocal = "@"

    '获取第一个订单编号的ID
    Do While intRangeIn <= intStop
        strStartRangeID = strRangeIn & intRangeIn
        strOutputRangeID = strRangeOut & intRangeOut
        strFinalSONo = ActiveWorkbook.Worksheets(xlWsh).Range(strStartRangeID).Value

        '切掉后面的订单序号
        If Not InStrRev(strFinalSONo, "-") = 0 Then
            strFinalSONo = cutEndSerialNumber(strFinalSONo)
        End If

        '切掉前面的非数字字符
        strFinalSONo = cutLeft(strFinalSONo)
        '切掉后面的
        strFinalSONo = cutRight(strFinalSONo)

        ActiveWorkbook.Worksheets(xlWsh).Range(strOutputRangeID).Value = strFinalSONo

        '继续下一行
        intRangeIn = intRangeIn + 1
        intRangeOut = intRangeOut + 1
    Loop


End Function

Private Function cutEndSerialNumber(SaleNumber As String) As String
    '-----------------截掉字符"-"后面的内容，包括"-"
    '但是如果包含多个"-"的话，每次只能截掉最后一个的
    '比方说cutEndSerialNumber("CHL0010-1-1")运行结果是CHL0010-1
    Dim intPosition As Integer
    intPosition = InStrRev(SaleNumber, "-")

    If intPosition = 0 Then
        cutEndSerialNumber = SaleNumber
        Exit Function
    End If

    Do While Not intPosition = 0
        SaleNumber = Left(SaleNumber, intPosition - 1)
        intPosition = InStrRev(SaleNumber, "-")
    Loop

    cutEndSerialNumber = SaleNumber
End Function
Private Function cutLeft(strInPut As String)
    '如果第一个就是数字的话就没必要切了
    If (Asc(Left(strInPut, 1)) >= Asc("0") And Asc(Left(strInPut, 1)) <= Asc("9")) Or Len(strInPut) = 1 Then
        cutLeft = strInPut
        Exit Function
    End If


    Do While Not (Asc(Left(strInPut, 1)) >= Asc("0") And Asc(Left(strInPut, 1)) <= Asc("9"))
        strInPut = Right(strInPut, Len(strInPut) - 1)
    Loop
    cutLeft = strInPut
End Function
Private Function cutRight(strInPut As String)
    '如果第一个就是数字的话就没必要切了
    If (Asc(Right(strInPut, 1)) >= Asc("0") And Asc(Right(strInPut, 1)) <= Asc("9")) Or Len(strInPut) = 1 Then
        cutRight = strInPut
        Exit Function
    End If



    Do While Not (Asc(Right(strInPut, 1)) >= Asc("0") And Asc(Right(strInPut, 1)) <= Asc("9"))
        strInPut = Left(strInPut, Len(strInPut) - 1)
    Loop
    cutRight = strInPut
End Function