EXCEL中使用VBA取有效使用区域

原创： 牛超
2009-06

OSAKA

 

如题，修改原来的脚本，引用UsedRange

 

Private Sub fillvalueauto(astr As String, curRow As Long)
Dim svalue As String
Dim sbatname As String
Dim spgid As String
Dim sbatnamew As String
Dim i As Long

 

astr = Trim(LCase(astr))
'MsgBox str(ThisWorkbook.Worksheets(1).Cells.Rows.Count)

For i = 1 To ThisWorkbook.Worksheets(1).UsedRange.Rows.Count
    svalue = ThisWorkbook.Worksheets(1).UsedRange.Cells(i, 1)
    svalue = Trim(LCase(svalue))
   
    If Len(Trim(svalue)) = 0 Then
        GoTo con
    End If
   
    If astr = svalue Then
        sbatname = Trim(ThisWorkbook.Worksheets(1).UsedRange.Cells(i, 1))
        spgid = Trim(ThisWorkbook.Worksheets(1).UsedRange.Cells(i, 3))
        spgidw = Trim(ActiveSheet.UsedRange.Cells(curRow, 8))
       
       
        ActiveSheet.UsedRange.Cells(curRow, 11) = spgid

        If Not LCase(spgidw) = LCase(spgid) Then
           
            ActiveSheet.UsedRange.Cells(curRow, 12) = "*"
        End If
        Exit For
       
    End If
con:
Next
If Not astr = svalue Then
    ActiveSheet.UsedRange.Cells(curRow, 13) = "XX"
End If
End Sub

 

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

Dim i As Integer
Dim flag As Boolean
Dim mindex As Long

'For mindex = 1 To 600
'        strget = ActiveSheet.Cells(mindex, 6)
For mindex = 1 To ActiveSheet.UsedRange.Rows.Count
        strget = ActiveSheet.UsedRange.Cells(mindex, 6)
        If Len(Trim(strget)) = 0 Then
            GoTo con
        End If
        If InStr(strget, ".bat") Then
            fillvalueauto strget, mindex
        End If
       
        'TextBox1.Text = TextBox1.Text & str(mindex) & Chr(13) & Chr(10)
con:
Next
End Sub