EXCEL遍历当前SHEET有效行，统计附值

原创： 牛超
2009-05
OSAKA

 

自动生成批处理名，规则为JOBID前三位+流水号

如题，放置按钮，遍历行，统计JOBID数，设置结果值

代码如下：

Private Type job
    jobid   As String
    cnt As Integer
End Type

Dim job(100) As job

Private Sub CommandButton1_Click()
Dim strget As String
Dim strret As String
Dim itmp As Integer
Dim i As Integer
Dim flag As Boolean

For mIndex = 1 To ActiveSheet.Cells.Rows.Count
    strget = ActiveSheet.Cells(mIndex, 4)
    If Not Left(strget, 1) = "R" Then
        GoTo con
    End If
    strget = Left(strget, 3)
    flag = False
       
    For i = 0 To 100
        If Len(job(i).jobid) = 0 Then
            Exit For
        End If
        If strget = job(i).jobid Then
            flag = True
            Exit For
        End If

    Next
    If Not flag Then
        job(i).jobid = strget
        job(i).cnt = 0
    End If
       
    If strget = job(i).jobid Then
        itmp = job(i).cnt
        itmp = itmp + 1
        job(i).cnt = itmp
        strret = RTrim(LTrim(Str(itmp)))
        strret = "00" + strret
        strret = Right(strret, 2)
        strret = strget + strret + ".bat"
        ActiveSheet.Cells(mIndex, 6) = strret
              
    End If
con:
Next

End Sub