I dont know why this errors but i assume its because it has a 'WITH' within the loop. I'm not sure how i can get around it so if anyone can help it would be appreciated.
Many thanks,
VBA Code:
' counts how many unique entries there are in a range
Sub dupez3()
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim shts As Integer
Dim a As Integer
shts = ThisWorkbook.Sheets.Count
a = 3
Do Until a = shts + 1
' Set ws = Worksheets("sheet" & a)
ws = Worksheets("sheet" & a)
' Set ws1 = Worksheets("Totals")
ws1 = Worksheets("Totals")
' Sheets("sheet2").Activate
With ws.Range("D2", ws.Range("D" & Rows.Count).End(xlUp))
ws1.Cells(3, 3) = ws.Evaluate("SUMPRODUCT(1/COUNTIF(" & .Address & "," & .Address & "))")
' MsgBox Evaluate("SUMPRODUCT(1/COUNTIF(" & .Address & "," & .Address & "))")
a = a + 1
Loop
End With
End Sub
Many thanks,