Excel VBA根据条件更新单元格填充

My code is in VBA and updates the value of a cell based off of two criteria. I've commented my code fairly extensively, so I'll paste it below first. I've changed comments by adding two more apostrophes to make is a bit easier to tell the difference between comments and code on this platform.

我的代码在VBA中,并根据两个条件更新单元格的值。我已经相当广泛地评论了我的代码,所以我先将它粘贴到下面。我通过添加两个更多的撇号来更改注释,以便更容易区分此平台上的注释和代码之间的区别。

Sub HighlightValues()

'''Shortcut key: ctrl + w

'''Highlights values of corresponding left-most cell, if two conditions are met:
'''The part is in "L" class and all of the rightmost cells are empty

Dim ws As Worksheet
Dim i As Long, lastrow As Long, lastcolumn As Long, c As Long, d As Long, j As Long, count As Long, k As Long, report As Long
Set ws = Sheets("QAP")

lastrow = ws.Cells(Rows.count, 1).End(xlUp).Row
lastcolumn = ws.Cells(1, ws.Columns.count).End(xlToLeft).Column

'''Searches column titles for "Analysis Task Count"
'''where it will start searching for filled boxes on each row
For i = 1 To lastrow
    If InStr(ws.Cells(1, i), "Analysis Task Count") Then
        '''c is the number of column that Analysis Task Count is in
        c = i - 1
    End If
Next

'''Searches column titles for "Required by RPC"
'''where it will search for whether the part is "L' or not
For d = 1 To lastcolumn
    If InStr(ws.Cells(1, d), "Required by RPC") Then
        '''k is the number of column that Required by RPC is in
        k = d
    End If
Next d

'''For each row that part data is in
For i = 11 To lastrow
    count = 0

    '''If any cells past Analysis Task Count are filled, remember that
    For j = c To lastcolumn
        If Not IsEmpty(ws.Cells(i, j)) Then
            count = 1
        End If
    Next j

    '''If the stage is L and all the cells are empty for that row
    If Cells(i, k).Value = ("L") And count = 0 Then
        '''Highlight the first box in green
        Cells(i, 1).Interior.Color = RGB(102, 255, 102)
    Else
        '''Or if thats not true, then make that box clear
        Cells(i, 1).Interior.Color = xlNone
        report = report + 1
    End If
Next i
MsgBox (report)
End Sub

To walk you through the code, (barring the spacing, some formatting didn't carry over), the code first searches for the column number of two critical columns, then in the last for loop, for each row, it searches for whether two criteria are met. If the criteria are met, a cell turns green, if not, it's filled in blank. I've spent about an hour confirming that the values returned by k and c are both the correct column number: I don't think that is the issue. Also, when I set up a count to see how many times the code goes through the last for loop, and each if statement, I get the correct number of cycles.

为了引导您完成代码,(除了间距,一些格式没有延续),代码首先搜索两个关键列的列号,然后在最后一个for循环中,对于每一行,它搜索是否有两个符合标准。如果满足标准,则单元格变为绿色,如果不满足,则填充空白。我花了大约一个小时确认k和c返回的值都是正确的列号:我不认为这是问题所在。此外,当我设置一个计数来查看代码经过最后一个for循环的次数,以及每个if语句时,我得到正确的循环次数。

This is very frustrating, the only weird thing is that the code executes nearly instantly, so there's no delay at all. And of course, the cell fill doesn't change. To test this, I ran the code filling in the cells with an orange color, and there was not change to any cells.

这非常令人沮丧,唯一奇怪的是代码几乎立即执行,因此根本没有延迟。当然,细胞填充不会改变。为了测试这一点,我运行了填充橙色单元格的代码,并且没有更改任何单元格。

Thank you for any help I can get!

谢谢你的帮助!

2 个解决方案

#1

The part responsible for searching for Analysis Task Count worksheet doesn't look correct to me.

负责搜索Analysis Task Count工作表的部分对我来说看起来不正确。

For i = 1 To lastrow         '<---- why [lastrow]? should be [lastcolumn]
    If InStr(ws.Cells(1, i), "Analysis Task Count") Then
        '''c is the number of column that Analysis Task Count is in
        c = i - 1            '<---- why subtracting 1?
    End If
Next

If you are searching through the column titles why do you use [lastrow] counter as upper bound for your loop. Also, I don't understand why do you subtract 1 after you find the column with such header.

如果您正在搜索列标题,为什么使用[lastrow]计数器作为循环的上限。此外,我不明白为什么你找到具有这样的标题的列后减去1。

Because of that you have incorrect column index and you compare incorrect sets of data.

因此,您的列索引不正确,并且您比较了不正确的数据集。

#2

1st. Change: ws.Cells(Rows.count, 1) To: ws.Cells(ws.Rows.count, 1) This has no effect, but "more right".

1。更改:ws.Cells(Rows.count,1)To:ws.Cells(ws.Rows.count,1)这没有效果,但“更正确”。

Hmmm

For i = 1 To lastrow
    If InStr(ws.Cells(1, i), "Analysis Task Count") Then
        c = i - 1

嗯因为我= 1拉斯特罗如果InStr(ws.Cells(1,i),“分析任务计数”)则 c = i - 1

Change lastrow to be lastcolumn Change c = i - 1 to c = i

将lastrow更改为lastcolumn将c = i - 1更改为c = i

Why start at 11 yet other loops start at 1.

为什么从11开始,其他循环从1开始。
```
    For i = 11 To lastrow
```
Please use more meaning full variables names - it's hard to read eg

请使用更多含义的完整变量名称 - 例如,它很难阅读
```
"iCol" for a column counter
"iRow" for a row counter
instead of "k" use "ColumnNumWithReqByRPVC"
etc..
```
Are you using instr correctly? See here

你正确使用instr吗?看这里
```
InStr([start, ]string1, string2[, compare])
```

Change

Cells(i, 1).Interior.Color 
To
ws.Cells(i, 1).Interior.Color

改变细胞(i,1).Interior.Color 至 ws.Cells(i,1).Interior.Color

Changing the above might make it work? let me know.

更改以上内容可能会有效吗?让我知道。

#1