时间戳

首页 > 解决方案 > 如何在代码中将数组函数与数组一起使用

vba - 如何在代码中将数组函数与数组一起使用

问题描述

以下是我为自动化工作中的某些流程而生成的一些代码的摘录。我制作的宏的一个元素是删除我报告范围之外的任何成绩。由于范围外的等级总是在变化，但范围内的等级是设置的，我决定尝试使用一个数组。我以前从未使用过这些，我在网上找到了一些代码用作模板，问题是代码似乎将所有等级标记为“假”，无论它们是否在数组中。我通过将删除更改为设置内部颜色来检查范围和列，这确认列和范围是正确的。我认为问题在于我没有正确地将函数与子代码中的代码链接起来。任何意见或建议将不胜感激：

Sub SortData()
Dim firstrow As Long
Dim LastRow As Integer
Dim arrGrades As Variant, grd As Variant

arrGrades = Array("Range B", "Range C", "Range D Experienced", "Range D", "Range E", "Range E2", "SCS 1", "SCS 2", "SCS 3", "Student")

With Sheets("Active OoD")
    .Select

    firstrow = .UsedRange.Cells(1).Row
    LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

    Set rng = Range("H2", "H" & LastRow)
    With rng
        For i = .Rows.Count To 1 Step -1
           If IsInArray(.Item(i), arrGrades) = False Then
                .EntireRow.Delete
            End If
        Next i
    End With
End With
End Sub

Function colNumeric(ColAlpha As String)
 ColAlpha = UCase(ColAlpha)
 If Len(ColAlpha) = 3 Then
     Col_no = (Asc(Left(ColAlpha, 1)) - 64) * 26 * 26 + _
              ((Asc(Mid(ColAlpha, 2, 1)) - 64) - 1) * 26 + _
              Asc(Right(ColAlpha, 1)) - 64
 ElseIf Len(ColAlpha) = 2 Then
     Col_no = (Asc(Left(ColAlpha, 1)) - 64) * 26 + _
              (Asc(Right(ColAlpha, 1)) - 64)
 Else
     Col_no = Asc(Right(ColAlpha, 1)) - 64
 End If
End Function

Function IsInArray(valToBeFound As Variant, arr As Variant) As Boolean
'Function IsInArray(grd As Variant, arrGrades As Variant) As Boolean
'INPUT: Pass the function a value to search for and an array of values of any data type.
'OUTPUT: True if is in array, false otherwise
Dim element As Variant
On Error GoTo IsInArrayError: 'array is empty
    For Each element In arr
        If element = valToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next element
Exit Function
IsInArrayError:
On Error GoTo 0
IsInArray = False
End Function

标签： vbaexcel

解决方案

这条线...

.EntireRow.Delete

...指整个范围的整行。因此，例如，如果Rng指的是 range H2:H10，EntireRow指的是$2:$10，因此所有内容都会被删除。相反，请尝试按如下方式引用当前行...

.Item(i).EntireRow.Delete

推荐阅读