End with without with ошибка vba - Решение и исправление самых разных ошибок на TopOshibok.ru

This can actually be caused by not having other control structures closed. The compiler can get confused and report this as a missing End With. Here are items to look for:

If/End If
Select/End Select
For/Next
Do/Loop
While/Wend

And of course,

With/End With

If you check to be sure each of these is closed properly, You’ll find the issue in your code.

As to why?

You can think of each of these structures like Russian nesting dolls. You have to close each one before putting in into a larger doll. Likewise, you have to open each doll in order to get the next smaller one.

Take this mental exercise a bit further for a moment. Try to imagine how you would manage to do otherwise? In other words, how would you place a doll inside the next larger doll without first closing it? You could just put in the bases and leave all the heads till the end, but that’s not how Russian nesting dolls work. You can’t put the heads on out of order.

That is what you are asking the compiler to do without closing each item in turn and a compiler can only take explicit instructions, so it can not determine your intent.

For a visual, consider this structure. It’s orderly and makes immediate logical sense. What makes sense is the nesting. Each structure is nested inside another structure.

--------+
--+     |
  |     |
--+     |
------+ |
----+ | |
    | | |
----+ | |
------+ |
--------+

Compare to this:

------+
--------+
--+   | |
--|-+ | |
--|-------+
  | | | | |
--+ | | | |
    | | | |
----|-+ | |
----+   | |
--------+ |
----------+

Try putting these nesting dolls back together by following these Steps:

Take the smallest body
Close it with one or more random head
Put this inside of the next larger body
Repeat

Ironman’s head on Nick Fury’s body and then put that into Captain America with the Hulk’s head and put all of that into the Thor’s body with Nick Fury’s head. And then put that into Hulk’s body. Good luck!

There’s only one step above that makes this process NOT work.

Источник

- Actions
  
  Automate any workflow
- Packages
  
  Host and manage packages
- Security
  
  Find and fix vulnerabilities
- Codespaces
  
  Instant dev environments
- Copilot
  
  Write better code with AI
- Code review
  
  Manage code changes
- Issues
  
  Plan and track work
- Discussions
  
  Collaborate outside of code
- GitHub Sponsors
  
  Fund open source developers
- The ReadME Project
  
  GitHub community articles
Pricing

Search code, repositories, users, issues, pull requests…

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Источник

Hi Fellows,

I am getting an «End With without With» compile error.
Can someone help please?

Code:

Sheets("Setup").Select
If (ActiveSheet.AutoFilterMode) Then ActiveSheet.AutoFilterMode = False
    Range("I4").Select
    xRow = Range("I4").End(xlDown).Row
        Range("F4:BU" & xRow).Select
        Selection.Name = "rawdata"
        Range("N3").Select
      x = ActiveCell.Address
 
Do Until ActiveCell.Value = ""
prop = ActiveCell
xCol = ActiveCell.Column
Sheets(prop).Select
sRow = (Range("F9").End(xlDown).Row) + 1
eRow = sRow + 2
    'Range("A" & eRow & ":H10000").Select
    'Selection.ClearContents
i = 9
    
    Sheets("Rawact").Select
     If (ActiveSheet.AutoFilterMode) Then ActiveSheet.AutoFilterMode = False
     Rows("2:10000").Select
     Selection.ClearContents
     
    Sheets("Setup").Select
    With Range("rawdata")
        .AutoFilter Field:=i, Criteria1:="Yes"
        xxRow = (Range("I4").End(xlDown).Row)
        Worksheets("Setup").Range("F4:M" & xxRow).Copy
        Sheets("Rawact").Range("A2").PasteSpecial _
               Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        Sheets("Rawact").Select
        raaRow = (Range("D2").End(xlDown).Row)
        Range("I2").Formula = "=IF(ISNA(VLOOKUP(""?????""&A2,INDIRECT(""" & prop & "!A1:A10000""),1,FALSE)),1,0)"
        Range("I2").Copy Range("I2:I" & raaRow)
 Application.Calculation = xlCalculationAutomatic
 
 If (ActiveSheet.AutoFilterMode) Then ActiveSheet.AutoFilterMode = False
   If Range("I1").Value > 0 Then
    
    
    With Range("A2:I" & raaRow)
        .AutoFilter Field:=9, Criteria1:="1"
        cRow = Range("D2").End(xlDown).Row
        Worksheets("Rawact").Range("A2:H" & cRow).Copy
        Sheets(prop).Select
Columns("A:C").Select
    Selection.EntireColumn.Hidden = False
firstrow = Columns(1).Find("*", Cells(2, 1), SearchDirection:=xlNext).Row
lastrow = Range("A" & firstrow).End(xlDown).Row
Range("A" & lastrow + 1).Select
End With
        
        Sheets(prop).Range("A" & lastrow + 1).PasteSpecial _
               Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        finalrow = Range("A" & firstrow).End(xlDown).Row
 
 

 
 For i = firstrow To finalrow
    Cells(i, "A").Value = Cells(1, "A").Value & Cells(i, "B").Value & Cells(i, "C").Value & Cells(i, "D").Value & Cells(i, "E").Value
Next i
 
 
    
Sheets("Setup").Select
If (ActiveSheet.AutoFilterMode) Then ActiveSheet.AutoFilterMode = False

ActiveCell.Offset(0, 1).Select
x = ActiveCell
i = i + 1
End With

Loop
   
Call Protect
End Sub

Add Bullets to Range

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

You’re missing an End If termination for this line

Code:

If Range("I1").Value > 0 Then

There may be other stuff like that, it’s hard to read as it’s not indented consistently.

Proper indentation of code will help identify these problems

You were missing an END IF (highlighted in RED)

Rich (BB code):

Sub test()
Sheets("Setup").Select
If (ActiveSheet.AutoFilterMode) Then ActiveSheet.AutoFilterMode = False
Range("I4").Select
xRow = Range("I4").End(xlDown).Row
Range("F4:BU" & xRow).Select
Selection.Name = "rawdata"
Range("N3").Select
x = ActiveCell.Address
Do Until ActiveCell.Value = ""
    prop = ActiveCell
    xCol = ActiveCell.Column
    Sheets(prop).Select
    sRow = (Range("F9").End(xlDown).Row) + 1
    eRow = sRow + 2
    'Range("A" & eRow & ":H10000").Select
    'Selection.ClearContents
    i = 9
 
    Sheets("Rawact").Select
    If (ActiveSheet.AutoFilterMode) Then ActiveSheet.AutoFilterMode = False
    Rows("2:10000").Select
    Selection.ClearContents
 
    Sheets("Setup").Select
    With Range("rawdata")
        .AutoFilter Field:=i, Criteria1:="Yes"
        xxRow = (Range("I4").End(xlDown).Row)
        Worksheets("Setup").Range("F4:M" & xxRow).Copy
        Sheets("Rawact").Range("A2").PasteSpecial _
        Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
        Sheets("Rawact").Select
        raaRow = (Range("D2").End(xlDown).Row)
        Range("I2").Formula = "=IF(ISNA(VLOOKUP(""?????""&A2,INDIRECT(""" & prop & "!A1:A10000""),1,FALSE)),1,0)"
        Range("I2").Copy Range("I2:I" & raaRow)
        Application.Calculation = xlCalculationAutomatic
        If (ActiveSheet.AutoFilterMode) Then ActiveSheet.AutoFilterMode = False
        If Range("I1").Value > 0 Then
            With Range("A2:I" & raaRow)
                .AutoFilter Field:=9, Criteria1:="1"
                cRow = Range("D2").End(xlDown).Row
                Worksheets("Rawact").Range("A2:H" & cRow).Copy
                Sheets(prop).Select
                Columns("A:C").Select
                Selection.EntireColumn.Hidden = False
                firstrow = Columns(1).Find("*", Cells(2, 1), SearchDirection:=xlNext).Row
                lastrow = Range("A" & firstrow).End(xlDown).Row
                Range("A" & lastrow + 1).Select
           End With
       End If
       Sheets(prop).Range("A" & lastrow + 1).PasteSpecial _
              Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
       finalrow = Range("A" & firstrow).End(xlDown).Row
 
 
 
        For i = firstrow To finalrow
            Cells(i, "A").Value = Cells(1, "A").Value & Cells(i, "B").Value & Cells(i, "C").Value & Cells(i, "D").Value & Cells(i, "E").Value
        Next i
 
        Sheets("Setup").Select
        If (ActiveSheet.AutoFilterMode) Then ActiveSheet.AutoFilterMode = False
        ActiveCell.Offset(0, 1).Select
        x = ActiveCell
        i = i + 1
    End With
Loop
 
Call Protect
End Sub

Hope that helps.

You need to indent your code properly so you can see the structure.

You have: If Range(«I1»).Value > 0 Then

with no corresponding End IF.

The With | End With pairs are matched but, because of the missing End If, VBA is getting confused. Not sure where the End If needs to go.

Regards

Источник

03-16-2014, 01:10 PM

#1

Forum Contributor

Compile Error «End With without With»

I am getting a «Compile Error» on the VBA code below, that says «End With without With».
Then it highlights a line in my code at the bottom area. (See where I labeled it with <<< below, on the code)
What am I doing wrong / missing?
03-16-2014, 01:20 PM

#2

Re: Compile Error «End With without With»

With the code properly indented, do you see the problem?

Entia non sunt multiplicanda sine necessitate
03-16-2014, 01:25 PM

#3

Forum Contributor

Re: Compile Error «End With without With»

No I do not. Very new to this. How can I fix it?
03-16-2014, 01:26 PM

#4

Re: Compile Error «End With without With»

You have one less End Select than you need.
03-16-2014, 01:30 PM

#5

Forum Contributor

Re: Compile Error «End With without With»

Ok, I removed one (See below) but I still get the error.
03-16-2014, 01:31 PM

#6

Forum Contributor

Re: Compile Error «End With without With»

Oh, You said one less? Where do I add it.
03-16-2014, 01:34 PM

#7

Forum Contributor

Re: Compile Error «End With without With»

Can you show me, how it should be?
03-16-2014, 01:36 PM

#8

Re: Compile Error «End With without With»
03-16-2014, 01:41 PM

#9

Forum Contributor

Re: Compile Error «End With without With»

Ok, Pasted in the fix, but I still get a de-bug error «Expected End Sub», highlighting «True» at the end.
Suggestions?
03-16-2014, 01:42 PM

#10

Re: Compile Error «End With without With»

BTW, I doubt you have fonts named «x», «RN», …
03-16-2014, 01:44 PM

#11

Re: Compile Error «End With without With»

The missing End Select goes just before the line: Case «i»:
03-16-2014, 01:47 PM

#12

Forum Contributor

Re: Compile Error «End With without With»

Sorry, very new to this. what should «fonts be shown as»?

Now I get no errors, but my bottom section of code (what I added to start this whole mess), «The One, Two, Three» part, does not work.
Any corrections you could make would be much appreciated.

I see what you mean about the fonts. Now changed to «arial».

Last edited by richard11153; 03-16-2014 at 01:52 PM.
03-16-2014, 01:51 PM

#13

Re: Compile Error «End With without With»

No idea what the macro should do but it works correctly.
03-16-2014, 01:57 PM

#14

Forum Contributor

Re: Compile Error «End With without With»

Well, I added the last section (that’s not working) The codes are putting a Letter, Word, or symbol, in the cell when clicked on,
depending on what letter is in the cell, just left of it. If you want to try and help me on this, I could send you
an attachment of a working sample.
03-16-2014, 02:02 PM

#15

Re: Compile Error «End With without With»

Exactly, it works with the letters «qwertyui» in a cell and it changes the cell on the right of the letter.
03-16-2014, 02:07 PM

#16

Forum Contributor

Re: Compile Error «End With without With»

Yes, But the bottom section, The «i» part that should put One, Two, Three, or Blank, in the cell,
is not working. Cells, next to the i, just stay blank. You see something there that’s wrong?
03-16-2014, 02:10 PM

#17

Forum Contributor

Re: Compile Error «End With without With»

Here is a working sample.
The last section of code, does not work properly
03-16-2014, 02:14 PM

#18

Re: Compile Error «End With without With»

As I said in post #11 your code should be:
03-16-2014, 02:22 PM

#19

Forum Contributor

Re: Compile Error «End With without With»

OK, Everything works fine now.
Not sure what I was doing wrong, or what the last paste fixed, but it works.
Thank you very much!!
03-16-2014, 02:27 PM

#20

Re: Compile Error «End With without With»

Even if VBA said that the «Compile Error» was «End With without With» you were missing a «End Select» as I indicated in post #11.
Glad it works.

Источник

Hello, I keep getting this error but I can’t work out why? Could someplease explain? Many Thanks in advance.

Sub Prepayjnlpastevalues()
'
' Prepayjnlpastevalues Macro
'
Dim LR As Long
With Sheets("Schedule")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A6:A" & LR).Copy
    Sheets("Journal").Range("A1").PasteSpecial Paste:=xlPasteValues
    
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("B6:B" & LR).Copy
    Sheets("Journal").Range("C1").PasteSpecial Paste:=xlPasteValues
    
    LR = .Range("AB" & Rows.Count).End(xlUp).Row
    .Range("AB6:AB" & LR).Copy
    Sheets("Journal").Range("D1").PasteSpecial Paste:=xlPasteValues
    
    LR = .Range("AF" & Rows.Count).End(xlUp).Row
    .Range("AF6:AF" & LR).Copy
    Sheets("Journal").Range("E1").PasteSpecial Paste:=xlPasteValues
Dim LastRow As Long, n As Long
With Sheets("Journal")
    LastRow = Range("D1000").End(xlUp).Row
    For n = LR To 1 Step -1
        If Cells(n, 4).Value = 0 Then Cells(n, 4).EntireRow.Delete
    
End With
End Sub

Display More

Re: Compile Error End With Without with

The lines:

For n = LR To 1 Step -1 
                If Cells(n, 4).Value = 0 Then Cells(n, 4).EntireRow.Delete 
                 
            End With

Should have and ‘End If’ rather than an ‘End With’. However, you still need the ‘End With’ to close the ‘With sheets…’ statement

With Sheets("Journal") 
            LastRow = Range("D1000").End(xlUp).Row 
            For n = LR To 1 Step -1 
                If Cells(n, 4).Value = 0 Then Cells(n, 4).EntireRow.Delete 


            End If                 
        End With 
    End Sub

- #3
Re: Compile Error End With Without with

cytop that If statement is a one liner, does not need End If
- #4
Re: Compile Error End With Without with

Hi,

Looks like one more «End With» & «Next» missing.
- #5
Re: Compile Error End With Without with

True — ‘Next’ instead of the ‘End If’

Need coffee to open eyes…

Re: Compile Error End With Without with

Untested, but I think this is compiled correctly

Sub Prepayjnlpastevalues()
     '
     ' Prepayjnlpastevalues Macro
     '
    Dim LR As Long
    Dim n As Long
    With Sheets("Schedule")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A6:A" & LR).Copy
        Sheets("Journal").Range("A1").PasteSpecial Paste:=xlPasteValues
         
        LR = .Range("B" & Rows.Count).End(xlUp).Row
        .Range("B6:B" & LR).Copy
        Sheets("Journal").Range("C1").PasteSpecial Paste:=xlPasteValues
         
        LR = .Range("AB" & Rows.Count).End(xlUp).Row
        .Range("AB6:AB" & LR).Copy
        Sheets("Journal").Range("D1").PasteSpecial Paste:=xlPasteValues
         
        LR = .Range("AF" & Rows.Count).End(xlUp).Row
        .Range("AF6:AF" & LR).Copy
        Sheets("Journal").Range("E1").PasteSpecial Paste:=xlPasteValues
    End With
    With Sheets("Journal")
        LR = .Range("D1000").End(xlUp).Row
        For n = LR To 1 Step -1
            If Cells(n, 4).Value = 0 Then Cells(n, 4).EntireRow.Delete
        Next n
    End With
End Sub

Display More

- #7
Re: Compile Error End With Without with

Yes it was the Next and a second End With — Thanks very much for your help!

Источник

Search code, repositories, users, issues, pull requests…

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»

Re: Compile Error «End With without With»