Loopy question
 

Have a spreadsheet sent to me
about 250 lines, 12 columns.

There are 60 lines of data, two blank rows
'nother 60 lines, two blank rows, etc.

After my numbers did not foot to the
published totals, discovered that the
person supplying the spreadsheet had
duplicated the same number
in the spreadsheet. (The odds of
having duplicate values in the same column
are extremely slight).

I want to loop through each cell, by column,
starting at cell D2 (data runs from D2 - O2)
store the value in cell D2 as a temporary variable,
compare the tmp variable to each cell in
the same column (going down).
If a match is found, msgbox
should say "OOPS!" (or something like that).
After cycling through all 60 lines (blank rows
separate the each set of 60 lines, so the cursor
will know when to terminate), the cursor
moves back to the top of the same column,
starts at D3, stores that
value, and compares the value in D3 to all cells
in the column directly below it, until a
blank row is found. By the time the cursor
starts comparing the last values in the columns
(such as cell D55/D56, etc.)
there would be only four values to compare with the
three cells below, then three, then two,
and finally the cursor will be comparing
the last value in the column only to itself.
( I suppose the process could terminate before
the last cell is reached).

Upon completing the column, the cursor moves
back to the top of the column, moves Right1,
and starts in column E with cell E2,
until all twelve columns have been audited.
Don't want to compare values between columns
(i.e. cell E55 with cell F55), just the cells in
column E with all the other cells below it in column E.
There are only 250 rows of data, so the whole
process should only take a few minutes.

Loopy question
 

It was unclear if you only wanted to check D,E and F. I check everything to
the right of C

Sub ABC()
Dim rng As Range, rng1 As Range
Dim rng3 As Range, rng4 As Range
Dim cell3 As Range, cell4 As Range

Set rng = Range(Cells(2, 4), Cells(Rows.Count, 4).End(xlUp))
Set rng1 = rng.SpecialCells(xlConstants)
For Each rng In rng1.Areas
Set rng3 = Range(rng(1), rng(1).End(xlToRight))
For Each cell3 In rng3
Set rng4 = Range(cell3, cell3.End(xlDown))
For Each cell4 In rng4
If Application.CountIf(rng4, cell4) 1 Then
cell4.Interior.ColorIndex = 3
Else
cell4.Interior.ColorIndex = xlNone
End If
Next
Next
Next

End Sub

--
Regards,
Tom Ogilvy

"J Shrimps, Jr." wrote in message
...
Have a spreadsheet sent to me
about 250 lines, 12 columns.

There are 60 lines of data, two blank rows
'nother 60 lines, two blank rows, etc.

After my numbers did not foot to the
published totals, discovered that the
person supplying the spreadsheet had
duplicated the same number
in the spreadsheet. (The odds of
having duplicate values in the same column
are extremely slight).

I want to loop through each cell, by column,
starting at cell D2 (data runs from D2 - O2)
store the value in cell D2 as a temporary variable,
compare the tmp variable to each cell in
the same column (going down).
If a match is found, msgbox
should say "OOPS!" (or something like that).
After cycling through all 60 lines (blank rows
separate the each set of 60 lines, so the cursor
will know when to terminate), the cursor
moves back to the top of the same column,
starts at D3, stores that
value, and compares the value in D3 to all cells
in the column directly below it, until a
blank row is found. By the time the cursor
starts comparing the last values in the columns
(such as cell D55/D56, etc.)
there would be only four values to compare with the
three cells below, then three, then two,
and finally the cursor will be comparing
the last value in the column only to itself.
( I suppose the process could terminate before
the last cell is reached).

Upon completing the column, the cursor moves
back to the top of the column, moves Right1,
and starts in column E with cell E2,
until all twelve columns have been audited.
Don't want to compare values between columns
(i.e. cell E55 with cell F55), just the cells in
column E with all the other cells below it in column E.
There are only 250 rows of data, so the whole
process should only take a few minutes.

Loopy question
 

Hey J,

I believe this is what you are looking for. If not keep it as for another
project..
enjoy... Rick

Option Explicit

Sub checkCells()
Dim c As Integer, r As Integer, rw As Integer
Dim temp As Variant
Dim ws As Worksheet

Set ws = ActiveSheet

For c = 4 To 15 Step 1 '' column count, D thru O
For r = 2 To 60 Step 1 '' row count
temp = ws.Cells(r, c).Value
rw = r + 1
Do Until IsEmpty(ws.Cells(rw, c).Value) '' loop until empty cell
If temp = ws.Cells(rw, c).Value Then '' compare cells
MsgBox ("Opps,we have a Match")
End If
rw = rw + 1
Loop
Next r
Next c

End Sub


"J Shrimps, Jr." wrote in message
...
Have a spreadsheet sent to me
about 250 lines, 12 columns.

There are 60 lines of data, two blank rows
'nother 60 lines, two blank rows, etc.

After my numbers did not foot to the
published totals, discovered that the
person supplying the spreadsheet had
duplicated the same number
in the spreadsheet. (The odds of
having duplicate values in the same column
are extremely slight).

I want to loop through each cell, by column,
starting at cell D2 (data runs from D2 - O2)
store the value in cell D2 as a temporary variable,
compare the tmp variable to each cell in
the same column (going down).
If a match is found, msgbox
should say "OOPS!" (or something like that).
After cycling through all 60 lines (blank rows
separate the each set of 60 lines, so the cursor
will know when to terminate), the cursor
moves back to the top of the same column,
starts at D3, stores that
value, and compares the value in D3 to all cells
in the column directly below it, until a
blank row is found. By the time the cursor
starts comparing the last values in the columns
(such as cell D55/D56, etc.)
there would be only four values to compare with the
three cells below, then three, then two,
and finally the cursor will be comparing
the last value in the column only to itself.
( I suppose the process could terminate before
the last cell is reached).

Upon completing the column, the cursor moves
back to the top of the column, moves Right1,
and starts in column E with cell E2,
until all twelve columns have been audited.
Don't want to compare values between columns
(i.e. cell E55 with cell F55), just the cells in
column E with all the other cells below it in column E.
There are only 250 rows of data, so the whole
process should only take a few minutes.