Just a code check please...

[ste mac]

Hi, this code should be checking a range on 'Datasheet' which starts
in B3...(3,2)

Please have a look at the code below, I have a feeling its
wrong but I am not sure...
Is this bit correct?

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)

or should it be

Set b1 = cell(2, 3)
Set b2 = cell(3, 3)
Set b3 = cell(4, 3)
Set b4 = cell(5, 3)
Set b5 = cell(6, 3)

Is it checking the correct range? I have this horrible feeling it
should be checking one cell to the right...
should it?

Option Explicit

Sub RebuildFULLhits()
Dim sheetname As String
Dim sheetnumber As Long

For sheetnumber = 1 To 56

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

RebulidFULLlinehits

Next
Application.ScreenUpdating = False
End Sub

Sub RebulidFULLlinehits()
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("Datasheet")
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
ball1 = 1
Else: data1 = 0
End If
If ActiveSheet.Cells(xlrow, 4).Value = b2 Then
ball2 = 1
Else: data2 = 0
End If
If ActiveSheet.Cells(xlrow, 5).Value = b3 Then
ball3 = 1
Else: data3 = 0
End If
If ActiveSheet.Cells(xlrow, 6).Value = b4 Then
ball4 = 1
Else: data4 = 0
End If
If ActiveSheet.Cells(xlrow, 7).Value = b5 Then
data5 = 1
Else: data5 = 0
End If
balltotal = data1 + data2 + data3 + data4 + data5
If balltotal = 0 Then
ActiveSheet.Cells(xlrow, 8).Value = ActiveSheet.Cells(xlrow, 8).Value
+ 1
ElseIf balltotal = 1 Then
ActiveSheet.Cells(xlrow, 9).Value = ActiveSheet.Cells(xlrow, 9).Value
+ 1
ElseIf balltotal = 2 Then
ActiveSheet.Cells(xlrow, 10).Value = ActiveSheet.Cells(xlrow,
10).Value + 1
ElseIf balltotal = 3 Then
ActiveSheet.Cells(xlrow, 11).Value = ActiveSheet.Cells(xlrow,
11).Value + 1
ElseIf balltotal = 4 Then
ActiveSheet.Cells(xlrow, 12).Value = ActiveSheet.Cells(xlrow,
12).Value + 1
ElseIf balltotal = 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
xlrow = xlrow + 1

Application.StatusBar = xlrow & " " & Activesheet.name
Loop
Application.StatusBar = False

Next cell
End Sub

 

[Bob Phillips]

It should be Cells, it is row then column, so it should be

Set b1 = cells(3,2)

and

Set b2 = cells(3, 3) or Set b2= Cells(4,2)

depending upon whether you want C3 or B4,

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Calvanese]

I think your code is much more complicated than it has to be. Could you post
exactly what you are tryint to accomplish. I may be able to help you if I
knew exactly what you are trying to do.

 

[Tom Ogilvy]

In your original code you had:

rng is set to B3 down to the last filled row -- on sheet Datasheet

the loop goes
for each cell in rng

so on the first turn, cell is B3

Let's see what cell(1,1) is

From the immediate window:
set cell = Range("B3")
? cell(1,1).Address, cell(1,2).Address, Cell(1,3).Address,
cell(1,4).Address, cell(1,5).Address
$B$3 $C$3 $D$3 $E$3 $F$3


You original code was:
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")

Looks like a match to me. But form your own opinion.

 

[ste mac]

Many thanks to Bob and Bob for your help...

Explanation:
I have 56 sheets with varying row amounts that I wish to
compare against 470'odd (changes in rows) rows in another sheet..
Tom O graciously altered the code to run the test (I was struggling)...

But after seeing the results I am not sure I have it quite right..
It checks all the rows of data on all 56 sheets against all the rows
of data on the 'Datasheet'the twist is... the matching value has to be
in the matching column ie.

a d e h k against d h k l n would give no matches because the values
are not in the same relative position...whereas
a d e h k against b d e k m would yield 2 matches as both 'd' and 'e' are
in the same relaitive postion...

I changed the code below (Thanks to Bob Philips)to what you can see now...
I am presuming that the values are now being checked for matches in the
correct relative postion? down the 'Datasheet' do you think so Bob(s)?

Set b1 = cell(2, 3)
Set b2 = cell(3, 3)
Set b3 = cell(4, 3)
Set b4 = cell(5, 3)
Set b5 = cell(6, 3)

thanks

ste

 

[ste mac]

Ah, now I see (I think) ...Tom, the 'B3' in ..set cell =
Range("B3")... becomes ...cell(1,1)...in the code is that right? this
is what I have not been able to understand, I kept thinkingit was
looking at the actual Cells(1,1) which would be A1... this was my
misunderstanding..

never knew about the intermediate window (now that is handy)....

I tried it out in the intermediate window and it was as you said:
$B$3 $C$3 $D$3 $E$3 $F$3
cell(1,1) cell(1,2) cell(1,3) cell(1,4) cell(1,5)

Tom, once again thanks for your help I am really glad asked this
question, learn't a couple of new things from this one.....

cheers

ste

 

[Tom Ogilvy]

cell(1,1) is short for cell.Item(1,1) Not to be confused with the cells
object (note the "s" on the end)

set cell = Range("B3")
? cell.Item(1,1).Address
$B$3


it could have been myrange if myrange was a reference to a range.
myrange(1,1)

cell(rowoffset, columnoffset). Unlike the offset property (zero based),
these offsets are 1 based. 1,1 refers to the cell itself.

so yes to what you say.

Also, it is the "immediate" window, not the "Intermediate" window - just
for purposes of talking about it to someone else.
It is because you can immediately evaluate a command or instruction.

 

[Bob Calvanese]

Are you saying that each cell in the 56 sheets have to match the same cell
on the data sheet?

Like... data sheet A1 = A1 on the other 56 sheets... and so on...?

If so... What do you want to happen if there is no match?
Do you want to force the match or put all the non-matches somewhere?

I don't quite understand the purpose of this. Could you give me some more
details?

Thanks

 

[Bob Calvanese]

If you are doing what I think you are, try this code...

Type CheckType
SheetName As String
RowNumber As String
ColumnNumber As String
CellValue As String
End Type
Dim CheckLog() As CheckType


Sub CheckRows()
Dim x As Integer, i As Integer, r As Integer, c As Integer
Dim CheckLogFlag As Boolean
CheckLogFlag = False
i = 0
'datasheet must be WorkSheet(1)
'loop through all sheets exept CheckLog sheet
For x = 2 To Worksheets.Count
r = 1
c = 1
'check for the CheckLog sheet
If Worksheets(x).Name <> "CheckLog" Then
With Worksheets(x)
'loop through rows
Do While .Cells(r, c) <> ""
' loop through columns
Do While .Cells(r, c) <> ""
'check data sheet cell to current sheet cell
'populate array of struct with error if one exists
If Worksheets(1).Cells(r, c) <> .Cells(r, c) Then
ReDim Preserve CheckLog(i)
CheckLog(i).SheetName = .Name
CheckLog(i).RowNumber = "Row: " & CStr(r)
CheckLog(i).ColumnNumber = "Column: " & CStr(c)
CheckLog(i).CellValue = CStr(.Cells(r, c))
i = i + 1
End If
'increment column
c = c + 1
Loop
' reset column and increment row
c = 1
r = r + 1
Loop
End With
Else
'skip CheckLog sheet and set flag
x = x + 1
CheckLogFlag = True
End If
Next x
r = 1
c = 1
' if flag not set then create CheckLog sheet
If CheckLogFlag = False Then
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Activate
Worksheets(Worksheets.Count).Name = "CheckLog"
End If
'activate checkLog sheet and populate with CheckLog struct data
Worksheets("CheckLog").Activate
With Worksheets("CheckLog")
.Cells.Select
Selection.ClearContents
.Cells(r, c).Select
If i > 0 Then
For x = 0 To UBound(CheckLog)
.Cells(r, c) = CheckLog(x).SheetName
.Cells(r, c + 1) = CheckLog(x).RowNumber
.Cells(r, c + 2) = CheckLog(x).ColumnNumber
.Cells(r, c + 3) = CheckLog(x).CellValue
r = r + 1
Next x
'clear the array of struct
Erase CheckLog
End If
End With
MsgBox ("Done With: " & i - 1 & " Errors")
End Sub

If not... Please explain more of what you are trying to do.

Hope this helps.

 

[ste mac]

Thanks Bob, I will definately try your code it will be worthwhile to
see how it works and the method...cheers...

Tom, immediate window...got it...now I think I understand, this thread has
been highly educational, there have been a number of things I just did not
know...and it is pleasing to have confirmed the code being run is producing
the correct results...

A big thanks to both you and Bob...

ste