[已解决]VBA用数组用for循环 查找等于52的所有单元格

laoau138

VBA用数组用for循环   查找等于52的所有单元格

最佳答案

月排行榜 / 总排行榜

wenzili

2017-3-12 20:29

Public Sub find52()
Dim alldata As Variant
Dim i As Integer
Dim n As Integer
Dim rg As Range
Set rg = Nothing


alldata = ActiveSheet.UsedRange.Value
For i = 1 To ActiveSheet.UsedRange.Rows.Count
    For n = 1 To ActiveSheet.UsedRange.Columns.Count
        If alldata(i, n) = 52 Then
            If rg Is Nothing Then
               Set rg = Cells(i, n)
            Else
               Set rg = Union(Cells(i, n), rg)
            End If
        End If
    Next
Next
rg.Select


End Sub

按你要求。不过，数组不如楼上直接单元格操作简便吧。

跳转到最佳答案楼层

砂海

Sub z52()

    For Each i In Range("a1:G10")
        If i.Value = 52 Then
            x = x + 1
            If x = 1 Then
                Set rg = i
            Else
                Set rg = Union(rg, i)
            End If
        End If
    Next i
   
    rg.Select
               
End Sub

laoau138

砂海 发表于 2017-3-11 17:52
Sub z52()

    For Each i In Range("a1:G10")

结果正确但没有用数组，更没有用for循环

wenzili

Public Sub find52()
Dim alldata As Variant
Dim i As Integer
Dim n As Integer
Dim rg As Range
Set rg = Nothing


alldata = ActiveSheet.UsedRange.Value
For i = 1 To ActiveSheet.UsedRange.Rows.Count
    For n = 1 To ActiveSheet.UsedRange.Columns.Count
        If alldata(i, n) = 52 Then
            If rg Is Nothing Then
               Set rg = Cells(i, n)
            Else
               Set rg = Union(Cells(i, n), rg)
            End If
        End If
    Next
Next
rg.Select


End Sub

按你要求。不过，数组不如楼上直接单元格操作简便吧。

laoau138

wenzili 发表于 2017-3-12 20:29
Public Sub find52()
Dim alldata As Variant
Dim i As Integer

果然高手继续努力啊