Counting cells if they match in two rows

[TGarrett]

Hi All,

I am using Excel 2007 and am working on a test grading macro. What I am trying to do is compare the test answers (E2:EQ2) to the student's answers (E10:EQ10) and have the overall score placed in a designated cell (ER10). I have seen a lot of posts regarding comparing two columns and have tried to apply that logic to the rows but I'm not quite getting it right. Unfortunately, the Excel file is on a closed network so I can't post it here (that would make it too easy ). This is what I have so far for the macro:

Sub TestScore ()

Dim i As String
Dim lastrow As String
Dim a As Integer

For a = 5 To 148
If ActiveSheet.Range(lastrow, a).value = ActiveSheet.Range(2, a).value Then
i = i + 1
End If
Next a
ActiveSheet.Range(lastrow, 149).value = i
End Sub

Any help would be greatly appreciated.

Tom

 

[RCBricker]

Hi All,

I am using Excel 2007 and am working on a test grading macro. What I am trying to do is compare the test answers (E2:EQ2) to the student's answers (E10:EQ10) and have the overall score placed in a designated cell (ER10). I have seen a lot of posts regarding comparing two columns and have tried to apply that logic to the rows but I'm not quite getting it right. Unfortunately, the Excel file is on a closed network so I can't post it here (that would make it too easy ). This is what I have so far for the macro:

Sub TestScore ()

Dim i As String
Dim lastrow As String
Dim a As Integer

For a = 5 To 148
If ActiveSheet.Range(lastrow, a).value = ActiveSheet.Range(2, a).value Then
i = i + 1
End If
Next a
ActiveSheet.Range(lastrow, 149).value = i
End Sub

Any help would be greatly appreciated.

Tom

try this

Sub TestScore()

 Dim lastrow As Long, lastcol As Long
 Dim a As Integer, i As Integer
 Dim ws As Worksheet
 Dim rngQ As Range, rngA As Range, rngSCORE
    
    Set ws = ActiveSheet
    ws.Select
    With ws
        i = 0
        lastcol = Cells(2, .Columns.Count).End(xlToLeft).Column
        Set rngSCORE = Cells(2, lastcol + 1)
        For a = 5 To lastcol
            lastrow = Cells(.Rows.Count, a).End(xlUp).Row
            Set rngQ = Cells(2, a)
            Set rngA = Cells(lastrow, a)
            If rngA.Value = rngQ.Value Then
                i = i + 1
            End If
        Next a
        rngSCORE.Value = i
    End With
 End Sub

 

[TGarrett]

RCBricker,

Worked like a charm. Thank you for your time and the help!

Tom