时间戳

首页 > 解决方案 > 使用 for 循环来识别行

excel - 使用 for 循环来识别行

问题描述

我在这里、这里和这里尝试过。

我试图根据第一列中单元格的字符串内容突出显示一行。

例如，如果第一列中的单元格包含字符串“Total”，则以较深的颜色突出显示该行。

Sub tryrow()
    Dim Years
    Dim rownum As String
    
    Years = Array("2007", "2008", "2009") ' short example 
    For i = 0 To UBound(Years)
        Set rownum = Range("A:A").Find(Years(i) & " Total", LookIn:=xlValues).Address
        Range(rownum, Range(rownum).End(xlToRight)).Interior.ColorIndex = 1
    Next i
End Sub

我收到此错误消息：

编译错误：需要对象

编辑器突出显示rownum = ，就好像这个对象没有用 初始化一样Dim rownum As String。

标签： excelvbafor-loop

解决方案

您在这里遇到了几个问题，如下所示：

Sub tryrow()
    Dim Years() As String 'Best practice is to dim all variables with types. This makes catching errors early much easier
    Dim rownum As Range 'Find function returns a range, not a string

    Years = Array("2007", "2008", "2009") ' short example
    For i = 0 To UBound(Years)
        Set rownum = Range("A:A").Find(Years(i) & " Total", LookIn:=xlValues) 'Return the actual range, not just the address of the range (which is a string)
        If Not rownum Is Nothing Then 'Make sure an actual value was found
            rownum.EntireRow.Interior.ColorIndex = 15 'Instead of trying to build row range, just use the built-in EntireRow function. Also, ColorIndex for gray is 15 (1 is black, which makes it unreadable)
        End If
    Next i
End Sub

推荐阅读