Help: Loop without Do, why?

[NRGZ]

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.

' 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,

 

[Phuoc]

Change: Do Until a = shts + 1

to: Do Until > shts

 

[NRGZ]

Change: Do Until a = shts + 1

to: Do Until > shts

nicer syntax but still errors.

 

[Fluff]

These two should be the other way round

Loop

End With

 

[Phuoc]

Sub dupez3()

Dim ws As Worksheet
Dim ws1 As Worksheet
Dim shts As Integer
Dim a As Integer

shts = ThisWorkbook.Sheets.Count
a = 3
Set ws1 = Worksheets("Totals")

Do Until a > shts
    Set ws = Worksheets("sheet" & a)
    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
    End With
Loop
End Sub