Need a bit of help on comparing cells

[ste mac]

Hi there, I have cobbled together the code below, it is supposed to
compare
the cell values of each row on 56 sheets by comparing it with the data
on
a sheet named 'Datasheet'...The data on the 'Datasheet' is 427 rows
deep but
will be added to sooner or later... I can get it to count the hits,
but it
only does that for the first line..I can't get it to check the other
426 rows
to give the total amount of hits over all 427+ lines...

any help is greatly appreciated...

ste

Option Explicit
Sub howmanyhits()
Dim sheetname As String
Dim sheetnumber As Long

For sheetnumber = 1 To 56

sheetname = "S" & Format(sheetnumber, "##0")
Sheets(sheetname).Select

howmanylinehits

Next
Application.ScreenUpdating = False
End Sub

Sub howmanylinehits()
Dim xlrow As Long
Dim data1 As Long
Dim data2 As Long
Dim data3 As Long
Dim data4 As Long
Dim data5 As Long
Dim datatotal As Long
Dim b1 As Range
Dim b2 As Range
Dim b3 As Range
Dim b4 As Range
Dim b5 As Range
Dim sheetnumber As Long

Set b1 = Sheets("Datasheet").Range("B3")
Set b2 = Sheets("Datasheet").Range("C3")
Set b3 = Sheets("Datasheet").Range("D3")
Set b4 = Sheets("Datasheet").Range("E3")
Set b5 = Sheets("Datasheet").Range("F3")

Application.ScreenUpdating = False
xlrow = 2

Do While Not (ActiveSheet.Cells(xlrow, 1).Value = "")
If ActiveSheet.Cells(xlrow, 3).Value = b1 Then
data1 = 1
Else: data1 = 0
End If
If ActiveSheet.Cells(xlrow, 4).Value = b2 Then
data2 = 1
Else: data2 = 0
End If
If ActiveSheet.Cells(xlrow, 5).Value = b3 Then
data3 = 1
Else: data3 = 0
End If
If ActiveSheet.Cells(xlrow, 6).Value = b4 Then
data4 = 1
Else: data4 = 0
End If
If ActiveSheet.Cells(xlrow, 7).Value = b5 Then
data5 = 1
Else: data5 = 0
End If
datatotal = data1 + data2 + data3 + data4 + data5
If datatotal = 0 Then
ActiveSheet.Cells(xlrow, 8).Value = ActiveSheet.Cells(xlrow, 8).Value
+ 1
ElseIf datatotal = 1 Then
ActiveSheet.Cells(xlrow, 9).Value = ActiveSheet.Cells(xlrow, 9).Value
+ 1
ElseIf datatotal = 2 Then
ActiveSheet.Cells(xlrow, 10).Value = ActiveSheet.Cells(xlrow,
10).Value + 1
ElseIf datatotal = 3 Then
ActiveSheet.Cells(xlrow, 11).Value = ActiveSheet.Cells(xlrow,
11).Value + 1
ElseIf datatotal = 4 Then
ActiveSheet.Cells(xlrow, 12).Value = ActiveSheet.Cells(xlrow,
12).Value + 1
ElseIf datatotal = 5 Then
ActiveSheet.Cells(xlrow, 13).Value = ActiveSheet.Cells(xlrow,
13).Value + 1
End If
ActiveSheet.Cells(xlrow, 14).Value = ActiveSheet.Cells(xlrow,
13).Value + ActiveSheet.Cells(xlrow, 12).Value _
+ ActiveSheet.Cells(xlrow, 11).Value + ActiveSheet.Cells(xlrow,
10).Value + ActiveSheet.Cells(xlrow, 9).Value
xlrow = xlrow + 1

Set b1 = b1.Offset(1, 0)
Set b2 = b2.Offset(1, 0)
Set b3 = b3.Offset(1, 0)
Set b4 = b4.Offset(1, 0)
Set b5 = b5.Offset(1, 0)

Application.StatusBar = xlrow & sheetnumber
Loop
Application.StatusBar = False

End Sub

 

[Tom Ogilvy]

Sub howmanylinehits()
Dim xlrow As Long
Dim data1 As Long
Dim data2 As Long
Dim data3 As Long
Dim data4 As Long
Dim data5 As Long
Dim datatotal As Long
Dim b1 As Range
Dim b2 As Range
Dim b3 As Range
Dim b4 As Range
Dim b5 As Range
Dim rng as Range
Dim cell as Range
Dim sheetnumber As Long

With Worksheets("DataSheets")
set rng = .range("B3",.Range("B3").End(xldown)
end with

Application.ScreenUpdating = False
for each cell in rng
xlrow = 2
set b1 = cell(1,1)
set b2 = cell(1,2)
set b3 = cell(1,3)
set b4 = cell(1,4)
set b5 = cell(1,5)

Do While Not (ActiveSheet.Cells(xlrow, 1).Value = "")
If ActiveSheet.Cells(xlrow, 3).Value = b1 Then
data1 = 1
Else: data1 = 0
End If
If ActiveSheet.Cells(xlrow, 4).Value = b2 Then
data2 = 1
Else: data2 = 0
End If
If ActiveSheet.Cells(xlrow, 5).Value = b3 Then
data3 = 1
Else: data3 = 0
End If
If ActiveSheet.Cells(xlrow, 6).Value = b4 Then
data4 = 1
Else: data4 = 0
End If
If ActiveSheet.Cells(xlrow, 7).Value = b5 Then
data5 = 1
Else: data5 = 0
End If
datatotal = data1 + data2 + data3 + data4 + data5
If datatotal = 0 Then
ActiveSheet.Cells(xlrow, 8).Value = ActiveSheet.Cells(xlrow, 8).Value
+ 1
ElseIf datatotal = 1 Then
ActiveSheet.Cells(xlrow, 9).Value = ActiveSheet.Cells(xlrow, 9).Value
+ 1
ElseIf datatotal = 2 Then
ActiveSheet.Cells(xlrow, 10).Value = ActiveSheet.Cells(xlrow,
10).Value + 1
ElseIf datatotal = 3 Then
ActiveSheet.Cells(xlrow, 11).Value = ActiveSheet.Cells(xlrow,
11).Value + 1
ElseIf datatotal = 4 Then
ActiveSheet.Cells(xlrow, 12).Value = ActiveSheet.Cells(xlrow,
12).Value + 1
ElseIf datatotal = 5 Then
ActiveSheet.Cells(xlrow, 13).Value = ActiveSheet.Cells(xlrow,
13).Value + 1
End If
ActiveSheet.Cells(xlrow, 14).Value = ActiveSheet.Cells(xlrow,
13).Value + ActiveSheet.Cells(xlrow, 12).Value _
+ ActiveSheet.Cells(xlrow, 11).Value + ActiveSheet.Cells(xlrow,
10).Value + ActiveSheet.Cells(xlrow, 9).Value
xlrow = xlrow + 1

Application.StatusBar = xlrow & sheetnumber
Loop
Application.StatusBar = False

Next cell
End Sub

 

[ste mac]

Tom, absolutely fantastic...thanks very much for your help...
spot on...

ste