开发者

excel vba :why doesn't this nested for loop do 1 to multiple comparison?

I have the following nexted for loop

'searches matches in Col C against B
For Row = 2 to totalrows Step 1

    'MsgBox "cell :" & Cells(Row, 2).Value
    For c = 2 To totalrows Step 1
        MsgBox " cell b :" & Cells(c, 2) & " cell C:" & Cells(rows, 3).Value
        If Cells(c, 3).Value = Cells(Row, 2).Value Then
            'change b color to orange = found
            With Cel开发者_如何学Pythonls(c, 2).Interior
                .ColorIndex = 4
                .Pattern = xlSolid
            End With

        End If

    Next c
Next Row

right now it compares b1 = c1 b2= c2 b3 = c3 instead of b1 = c1 b1 = c2 b1 = c3 b2 = c1 b2 = c2 ...

am i missing something?


In msgbox, when you get the value of cell b you missed the .value, And when you get the value of cell c you wrote cells(rows, 3), which is wrong, should be cells(row,3). Probably (since other's posts code are very similar to yours) it works fine but the message boxes it shows are wrong because of this line of code.


Sub a()
totalrows = 3
'searches matches in Col C against B
For Row = 2 To 2 + totalrows Step 1

    'MsgBox "cell :" & Cells(Row, 2).Value
    For c = 2 To 2 + totalrows Step 1
        MsgBox " cell B :" & Cells(c, 2).Value & " cell C:" & Cells(Row, 3).Value
        If Cells(c, 3).Value = Cells(Row, 2).Value Then
            'change b color to GREEN = found
            With Cells(c, 2).Interior
                .ColorIndex = 4
                .Pattern = xlSolid
            End With

        End If

    Next c
Next Row
End Sub


Edit: Maybe I should mention that what was in the msgbox and what was compared was not the same and I changed this.

I work with debug.print and open the immediate window that helps. I have this in my sheet

a1  b1  c1  d1
a2  b2  c2  d2
a3  b3  c3  d3
a4  b4  c4  d4

This is the code

Sub mysub()

totalrows = 4

'searches matches in Col C against B
For Row = 1 To totalrows Step 1

    Debug.Print "row = " & Row
    'MsgBox "cell :" & Cells(Row, 2).Value
    For c = 1 To totalrows Step 1
        'MsgBox " cell b :" & Cells(c, 2) & " cell C:" & Cells(Row, 3).Value
        Debug.Print " cell b :" & Cells(Row, 2).Value & " cell C:" & Cells(c, 3).Value
        If Cells(Row, 2).Value = Cells(c, 3).Value Then
            'change b color to orange = found
            With Cells(c, 2).Interior
                .ColorIndex = 4
                .Pattern = xlSolid
            End With

        End If

    Next c
Next Row
End Sub

This is the result

row = 1
 cell b :b1 cell C:c1
 cell b :b1 cell C:c2
 cell b :b1 cell C:c3
 cell b :b1 cell C:c4
row = 2
 cell b :b2 cell C:c1
 cell b :b2 cell C:c2
 cell b :b2 cell C:c3
 cell b :b2 cell C:c4
row = 3
 cell b :b3 cell C:c1
 cell b :b3 cell C:c2
 cell b :b3 cell C:c3
 cell b :b3 cell C:c4
row = 4
 cell b :b4 cell C:c1
 cell b :b4 cell C:c2
 cell b :b4 cell C:c3
 cell b :b4 cell C:c4
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜