Hi Toppers, thanks for th enew code, works great.

would you mind me making a slight amendment, as the way I want it to work has
slightly changed.

My spreadsheet has many sheets, Sheet 1 called "Derby", sheet 2 called
"Sheffield" etc with a Sheet called "Voucher Descrep".

If I run the macro from each individual sheet, is it possible to show all the
results on one sheet,

So we would see on the "voucher Descrep" sheet

Columns A:C would be column A- "Derby" (Sheet Name) column B "missing" and
column C "duplicated" and miss a column then Columns E:G would be column E-
"Sheffield" (Sheet Name) column F "missing" and column G "duplicated" etc,

sorry not to have been more clearer from the start,

Thanks for your help so far,

Cheers

David



Toppers wrote:
Hi,

Use "With activesheet" but make sure it is the active sheet when you run!

And a slightly tidier version:

Sub FindMissingAndDuplicates()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim v() As Long, missing() As Long, i As Long, lastrow As Long

sblock = Application.InputBox("Enter block start")
fblock = Application.InputBox("Enter block end")

ReDim v(1 To fblock - sblock + 1)

j = 0
For i = sblock To fblock
j = j + 1
v(j) = i
Next i

Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
ws2.Range("a1:b1") = Array("Missing", "Duplicated")

With ActiveSheet
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
Set rng = .Range("a1:a" & lastrow)
End With

n1 = 2
n2 = 2
For i = LBound(v) To UBound(v)
num = Application.CountIf(rng, v(i))
Select Case num
Case Is = 0
ws2.Cells(n1, 1) = v(i)
n1 = n1 + 1
Case Is 1
ws2.Cells(n2, 2) = v(i)
n2 = n2 + 1
End Select
Next i
End Sub

Toppers,

[quoted text clipped - 55 lines]

regards

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200602/1