'No Next without For' not sure why? (code within)

NRGZ

New Member
Joined
Jul 23, 2021
Messages
47
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
I cant work out why it is telling me this as i think i have it on the order but obviously not!
VBA Code:
Sub dupez2()

Dim ws As Worksheet
Dim ws1 As Worksheet
Dim rowz As Integer
Dim a As Integer
Dim b As Integer
Dim c As Integer

Set ws = Worksheets("sheet2")
Set ws1 = Worksheets("Totals")
rowz = ws1.Range("A2").End(xlDown).Row
a = ThisWorkbook.Sheets.Count

b = 3

c = 2


For b = 3 To a
    For c = 2 To rowz
    
' ws = Worksheets("sheet" + b)


' Sheets("sheet2").Activate

    With ws.Range("D2", ws.Range("D" & rows.Count).End(xlUp))
   
    ws1.Cells(c, 3) = Worksheets("sheet" & b).Evaluate("SUMPRODUCT(1/COUNTIF(" & .Address & "," & .Address & "))")
   
   ' MsgBox Evaluate("SUMPRODUCT(1/COUNTIF(" & .Address & "," & .Address & "))")

b = b + 1

If b > a Then End

Next b
Next c

End With
Thanks.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hi,

Your Next statement are nested in WITH section.
The "End With" should be above both Next statements
 
Upvote 0
Solution
The order of your closing statements is incorrect on both your FOR and WITH statements.

When you use things that have a starting and ending statements like For/Next and With/End With, you must close them in the reverse order that they occur in (so the last one added is the first one closed, and the first one added is the last one closed.

So the structure should be:
VBA Code:
For b
    For c
        With
        End With
    Next c
Next b
 
Upvote 0
Thanks alot guys it had to be something simple.
Its a shame i cant split the solution tick between the both of you but as Joe has squllions of messages i'll award it to Jori and encouragement
Thanks,
o/
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top