End With Without With? What am I missing here?

E

ERLoft

Board Regular
Joined
Feb 24, 2006
Messages
193
Getting a compile error for End With without With. The whole code section is probably too long to post, but if someone would want to see the entire thing, please let me know and I'll post it.

Here's the section of code in question:

Code: 
ElseIf Cells(2, 1).Value > CheckMonthEnd Then
        'This code handles the update if the update date is after the month end date.
        
        Call FindDailyStatisticsWorkbook
        
        'Check to see if this is the correct month Daily Statistics workbook.
        With Sheets("Stats")
            If (Range("A5") <> CurrentMonthToUpdate) Then
                MsgBox ("You don't have last month's Daily Statistics spreadsheet open, please open it now")
                    FileToOpen = Application.GetOpenFilename _
                    (Title:="Please select your Daily Statistics report for last month", _
                    FileFilter:="Excel Files *.xlsx (*.xlsx) *.xls (.xls),")
    
                If FileToOpen = False Then
                    MsgBox "No file specified.", vbExclamation, "Duh!!!"
                Exit Sub
            End If
        
                    
            For RowNumber = .UsedRange.Rows.Count To 5 Step -1
                If (Range("A" & RowNumber) = StartHistoryDateToGrab) Then
                    Cells(RowNumber, 1).Activate
                End If
            Next RowNumber
            
            'Grab the data
            MonthEndRooms = Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(EndHistoryDateToGrab - (EndHistoryDateToGrabInteger - 1), 1))
            MonthEndADR = Range(ActiveCell.Offset(0, 8), ActiveCell.Offset(EndHistoryDateToGrab - (EndHistoryDateToGrabInteger - 1), 8))
        End With
        
        'Now place the data in the approprate places on the yield sheet.
        Workbooks(YieldSheet).Activate
            
        With ActiveSheet
            Range(ActiveCell.Offset(5, (StartHistoryDateToGrabInteger - 1)), ActiveCell.Offset(5, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(MonthEndRooms)
            Range(ActiveCell.Offset(14, (StartHistoryDateToGrabInteger - 1)), ActiveCell.Offset(14, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(MonthEndGroupRooms)
            Range(ActiveCell.Offset(8, (StartHistoryDateToGrabInteger - 1)), ActiveCell.Offset(8, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(MonthEndADR)
        End With

I see two instances of With, but both have an End With. What am I missing here?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Yeah, I think we need to see the whole thing.
Simply because we're going to get other 'compile errors' when pasting that into our own vba windows.
Because the posted code begins with 'ElseIF'
 
Upvote 0
Actually, I think you're missing an End If
Rich (BB code): 
            If (Range("A5") <> CurrentMonthToUpdate) Then
                MsgBox ("You don't have last month's Daily Statistics spreadsheet open, please open it now")
                    FileToOpen = Application.GetOpenFilename _
                    (Title:="Please select your Daily Statistics report for last month", _
                    FileFilter:="Excel Files *.xlsx (*.xlsx) *.xls (.xls),")
    
                If FileToOpen = False Then
                    MsgBox "No file specified.", vbExclamation, "Duh!!!"
                End If
                Exit Sub
            End If
 
Upvote 0
I added the End If. Message was for End With however. I'll try running the code as it is now to see if it works.

Here's full Sub - note that most of the variables used are declared as Public variables in another module.

Code: 
Sub YieldSheetUpdate()

Dim RowNumber As Long
Dim WeAreHere As String
Dim wb As Workbook
Dim bFlag As Boolean

'Check to see if the update run date for the spreadsheet is before or after the first of the month for this tab.
'If it's after, then we want to grab the data for the full month to update.
'If it's before, we need to use the StartHistoryDateToGrab and EndHistoryDateToGrab variables to grab the dates in the past.


If Cells(2, 1).Value < Cells(3, 2).Value Then
        'This code handles the update if the entire month is being updated.  This part has been updated for Choice Advantage.
        
        Call FindHandFWorkbook
        
        With Sheets("Sheet1")
            For RowNumber = .UsedRange.Rows.Count To 1 Step -1
                If (Range("A" & RowNumber) = CurrentMonthToUpdate) Then
                    Cells(RowNumber, 1).Activate
                End If
            Next RowNumber
            
            'Grab the data.
            FullMonthRooms = Range(ActiveCell.Offset(0, 12), ActiveCell.Offset((LastDayOfMonth - 1), 12))
            FullMonthGroupRooms = Range(ActiveCell.Offset(0, 8), ActiveCell.Offset((LastDayOfMonth - 1), 8))
            FullMonthADR = Range(ActiveCell.Offset(0, 16), ActiveCell.Offset((LastDayOfMonth - 1), 16))
            
            'Subtract group rooms from full rooms to get transient room totals.
            ReDim FullMonthTransientRooms(LBound(FullMonthRooms, 1) To UBound(FullMonthRooms, 1), LBound(FullMonthRooms, 2) To UBound(FullMonthRooms, 2))
                For i = LBound(FullMonthRooms, 1) To UBound(FullMonthRooms, 1)
                    For j = LBound(FullMonthRooms, 2) To UBound(FullMonthRooms, 2)
                        FullMonthTransientRooms(i, j) = FullMonthRooms(i, j) - FullMonthGroupRooms(i, j)
                    Next j
                Next i
            
            'Now place the data in the appropriate places on the yield sheet.
            Workbooks(YieldSheet).Activate
            
            With ActiveSheet
                Range(ActiveCell.Offset(5, 0), ActiveCell.Offset(5, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(FullMonthTransientRooms)
                Range(ActiveCell.Offset(14, 0), ActiveCell.Offset(14, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(FullMonthGroupRooms)
                Range(ActiveCell.Offset(8, 0), ActiveCell.Offset(8, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(FullMonthADR)
            End With
        End With
        
ElseIf Cells(2, 1).Value > Cells(3, 2).Value And Cells(1, 1).Value < Cells(3, 2).Value Then
        'This code handles the update for the current month when the last update was done in the prior month. This part has been updated for Choice Advantage.
        
        'This portion will pull the historical data from the Daily Statistics workbook.
        Call FindDailyStatisticsWorkbook
        
        With Sheets("Stats")
            For RowNumber = .UsedRange.Rows.Count To 5 Step -1
                If (Range("A" & RowNumber) = StartHistoryDateToGrab) Then
                    Cells(RowNumber, 1).Activate
                End If
            Next RowNumber
            
            HistoryRooms = Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(EndHistoryDateToGrab - (EndHistoryDateToGrabInteger - 1), 1))
            HistoryADR = Range(ActiveCell.Offset(0, 8), ActiveCell.Offset(EndHistoryDateToGrab - (EndHistoryDateToGrabInteger - 1), 8))
        End With
                
        'Pull today and future dates from the Occupancy Snapshot report.
        Call FindHandFWorkbook
        
        With Sheets("Sheet1")
            'Grab the data.
            Range("A5").Select
            
            RemainingCurrentMonthRooms = Range(ActiveCell.Offset(0, 12), ActiveCell.Offset((LastDayOfMonth - (EndHistoryDateToGrabInteger - 1)), 12))
            RemainingCurrentMonthGroupRooms = Range(ActiveCell.Offset(0, 8), ActiveCell.Offset((LastDayOfMonth - (EndHistoryDateToGrabInteger - 1)), 8))
            RemainingCurrentMonthADR = Range(ActiveCell.Offset(0, 16), ActiveCell.Offset((LastDayOfMonth - (EndHistoryDateToGrabInteger - 1)), 16))
            
            'Subtract group rooms from full rooms to get transient room totals.
            ReDim RemainingCurrentMonthTransientRooms(LBound(RemainingCurrentMonthRooms, 1) To UBound(RemainingCurrentMonthRooms, 1), LBound(RemainingCurrentMonthRooms, 2) To UBound(RemainingCurrentMonthRooms, 2))
                For i = LBound(RemainingCurrentMonthRooms, 1) To UBound(RemainingCurrentMonthRooms, 1)
                    For j = LBound(RemainingCurrentMonthRooms, 2) To UBound(RemainingCurrentMonthRooms, 2)
                        RemainingCurrentMonthTransientRooms(i, j) = RemainingCurrentMonthRooms(i, j) - RemainingCurrentMonthGroupRooms(i, j)
                    Next j
                Next i
            
            'Now place these values in the appropriate places on the yield sheet.
            Workbooks(YieldSheet).Activate
        
            With ActiveSheet
                Range(ActiveCell.Offset(5, 0), ActiveCell.Offset(5, (EndHistoryDateToGrabInteger - 2))).Value = Application.WorksheetFunction.Transpose(HistoryRooms)
                Range(ActiveCell.Offset(14, 0), ActiveCell.Offset(14, (EndHistoryDateToGrabInteger - 2))).Value = Application.WorksheetFunction.Transpose(HistoryGroupRooms)
                Range(ActiveCell.Offset(8, 0), ActiveCell.Offset(8, (EndHistoryDateToGrabInteger - 2))).Value = Application.WorksheetFunction.Transpose(HistoryADR)
                Range(ActiveCell.Offset(5, (EndHistoryDateToGrabInteger - 1)), ActiveCell.Offset(5, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(RemainingCurrentMonthRooms)
                Range(ActiveCell.Offset(14, (EndHistoryDateToGrabInteger - 1)), ActiveCell.Offset(14, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(RemainingCurrentMonthGroupRooms)
                Range(ActiveCell.Offset(8, (EndHistoryDateToGrabInteger - 1)), ActiveCell.Offset(8, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(RemainingCurrentMonthADR)
            End With
            
        End With

        
ElseIf Cells(2, 1).Value > CheckMonthEnd Then
        'This code handles the update if the update date is after the month end date.
        
        Call FindDailyStatisticsWorkbook
        
        'Check to see if this is the correct month Daily Statistics workbook.
        With Sheets("Stats")
            If (Range("A5") <> CurrentMonthToUpdate) Then
                MsgBox ("You don't have last month's Daily Statistics spreadsheet open, please open it now")
                    FileToOpen = Application.GetOpenFilename _
                    (Title:="Please select your Daily Statistics report for last month", _
                    FileFilter:="Excel Files *.xlsx (*.xlsx) *.xls (.xls),")
    
                If FileToOpen = False Then
                    MsgBox "No file specified.", vbExclamation, "Duh!!!"
                    Exit Sub
                End If
            End If
        
                    
            For RowNumber = .UsedRange.Rows.Count To 5 Step -1
                If (Range("A" & RowNumber) = StartHistoryDateToGrab) Then
                    Cells(RowNumber, 1).Activate
                End If
            Next RowNumber
            
            'Grab the data
            MonthEndRooms = Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(EndHistoryDateToGrab - (EndHistoryDateToGrabInteger - 1), 1))
            MonthEndADR = Range(ActiveCell.Offset(0, 8), ActiveCell.Offset(EndHistoryDateToGrab - (EndHistoryDateToGrabInteger - 1), 8))
        End With
        
        'Now place the data in the approprate places on the yield sheet.
        Workbooks(YieldSheet).Activate
            
        With ActiveSheet
            Range(ActiveCell.Offset(5, (StartHistoryDateToGrabInteger - 1)), ActiveCell.Offset(5, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(MonthEndRooms)
            Range(ActiveCell.Offset(14, (StartHistoryDateToGrabInteger - 1)), ActiveCell.Offset(14, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(MonthEndGroupRooms)
            Range(ActiveCell.Offset(8, (StartHistoryDateToGrabInteger - 1)), ActiveCell.Offset(8, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(MonthEndADR)
        End With
        
Else
        'This handles current month updates.  It finds the rooms and ADR between the last update and current date, then
        'moves it to the Yield sheet.
        
        'This portion will pull the historical data from the Daily Statistics workbook.
        
        Call FindDailyStatisticsWorkbook
        
        With Sheets("Stats")
            For RowNumber = .UsedRange.Rows.Count To 5 Step -1
                If (Range("A" & RowNumber) = StartHistoryDateToGrab) Then
                    Cells(RowNumber, 1).Activate
                End If
            Next RowNumber
            
            HistoryRooms = Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(EndHistoryDateToGrab - (EndHistoryDateToGrabInteger - 1), 1))
            HistoryADR = Range(ActiveCell.Offset(0, 8), ActiveCell.Offset(EndHistoryDateToGrab - (EndHistoryDateToGrabInteger - 1), 8))
        End With
        
        Call FindHandFWorkbook
        
        With Sheets("Sheet1")
            'Get the transient and group rooms and ADR for today through the end of the month.
            
            Range("A5").Select
            
            RemainingCurrentMonthRooms = Range(ActiveCell.Offset(0, 12), ActiveCell.Offset((LastDayOfMonth - (EndHistoryDateToGrabInteger - 1)), 12))
            RemainingCurrentMonthGroupRooms = Range(ActiveCell.Offset(0, 8), ActiveCell.Offset((LastDayOfMonth - (EndHistoryDateToGrabInteger - 1)), 8))
            RemainingCurrentMonthADR = Range(ActiveCell.Offset(0, 16), ActiveCell.Offset((LastDayOfMonth - (EndHistoryDateToGrabInteger - 1)), 16))
            
            'Subtract group rooms from full rooms to get transient room totals.
            ReDim RemainingCurrentMonthTransientRooms(LBound(RemainingCurrentMonthRooms, 1) To UBound(RemainingCurrentMonthRooms, 1), LBound(RemainingCurrentMonthRooms, 2) To UBound(RemainingCurrentMonthRooms, 2))
                For i = LBound(RemainingCurrentMonthRooms, 1) To UBound(RemainingCurrentMonthRooms, 1)
                    For j = LBound(RemainingCurrentMonthRooms, 2) To UBound(RemainingCurrentMonthRooms, 2)
                        RemainingCurrentMonthTransientRooms(i, j) = RemainingCurrentMonthRooms(i, j) - RemainingCurrentMonthGroupRooms(i, j)
                    Next j
                Next i
            
            'Now place these values in the appropriate places on the yield sheet.
            Workbooks(YieldSheet).Activate
        
            With ActiveSheet
                Range(ActiveCell.Offset(5, (StartHistoryDateToGrabInteger - 1)), ActiveCell.Offset(5, (EndHistoryDateToGrabInteger - 1))).Value = Application.WorksheetFunction.Transpose(HistoryRooms)
                Range(ActiveCell.Offset(14, (StartHistoryDateToGrabInteger - 1)), ActiveCell.Offset(14, (EndHistoryDateToGrabInteger - 1))).Value = Application.WorksheetFunction.Transpose(HistoryGroupRooms)
                Range(ActiveCell.Offset(8, (StartHistoryDateToGrabInteger - 1)), ActiveCell.Offset(8, (EndHistoryDateToGrabInteger - 1))).Value = Application.WorksheetFunction.Transpose(HistoryADR)
                Range(ActiveCell.Offset(5, (EndHistoryDateToGrabInteger - 1)), ActiveCell.Offset(5, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(RemainingCurrentMonthRooms)
                Range(ActiveCell.Offset(14, (EndHistoryDateToGrabInteger - 1)), ActiveCell.Offset(14, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(RemainingCurrentMonthGroupRooms)
                Range(ActiveCell.Offset(8, (EndHistoryDateToGrabInteger - 1)), ActiveCell.Offset(8, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(RemainingCurrentMonthADR)
            End With
        End With
        
End If

End Sub
 
Upvote 0
ERLoft said:
Message was for End With however.
Click to expand...
I'm aware of that.
It may have been a 'misplaced' end if (unable to tell without the whole code)

It seems strange, but that can cause that error.
A Missing/misplaced End If CAN cause a End With without With Error
And Vice versa.
A missing/misplaced End With can cause a End If without If error.

Not sure why it happens, something about the order that vba compiles IFs and Withs.

If you start an IF Inside a With, that IF MUST end Inside the With
And vice versa
If you start a With inside an If, that With must end inside that If

Let me take a look at that code...
 
Last edited:
Upvote 0
ERLoft said:
Getting a compile error for End With without With. The whole code section is probably too long to post, but if someone would want to see the entire thing, please let me know and I'll post it.

Here's the section of code in question:

Rich (BB code): 
ElseIf Cells(2, 1).Value > CheckMonthEnd Then
        'This code handles the update if the update date is after the month end date.
        
        Call FindDailyStatisticsWorkbook
        
        'Check to see if this is the correct month Daily Statistics workbook.
        With Sheets("Stats")
            If (Range("A5") <> CurrentMonthToUpdate) Then
                MsgBox ("You don't have last month's Daily Statistics spreadsheet open, please open it now")
                    FileToOpen = Application.GetOpenFilename _
                    (Title:="Please select your Daily Statistics report for last month", _
                    FileFilter:="Excel Files *.xlsx (*.xlsx) *.xls (.xls),")
    
                If FileToOpen = False Then
                    MsgBox "No file specified.", vbExclamation, "Duh!!!"
                Exit Sub
            End If
        
                    
            For RowNumber = .UsedRange.Rows.Count To 5 Step -1
                If (Range("A" & RowNumber) = StartHistoryDateToGrab) Then
                    Cells(RowNumber, 1).Activate
                End If
            Next RowNumber
            
            'Grab the data
            MonthEndRooms = Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(EndHistoryDateToGrab - (EndHistoryDateToGrabInteger - 1), 1))
            MonthEndADR = Range(ActiveCell.Offset(0, 8), ActiveCell.Offset(EndHistoryDateToGrab - (EndHistoryDateToGrabInteger - 1), 8))
        End With
        
        'Now place the data in the approprate places on the yield sheet.
        Workbooks(YieldSheet).Activate
            
        With ActiveSheet
            Range(ActiveCell.Offset(5, (StartHistoryDateToGrabInteger - 1)), ActiveCell.Offset(5, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(MonthEndRooms)
            Range(ActiveCell.Offset(14, (StartHistoryDateToGrabInteger - 1)), ActiveCell.Offset(14, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(MonthEndGroupRooms)
            Range(ActiveCell.Offset(8, (StartHistoryDateToGrabInteger - 1)), ActiveCell.Offset(8, (LastDayOfMonth - 1))).Value = Application.WorksheetFunction.Transpose(MonthEndADR)
        End With

I see two instances of With, but both have an End With. What am I missing here?
Click to expand...
VB cannot distinguish between block codes when evaluating missing End statements, so it reports on the last block it was processing at the time it saw the problem. Just because it says the With statement isn't closed does not necessarily mean that is where the problem is. In your case, the red highlighted text is an If..Then block that is missing the End If statement needed to close that block. If you add the End If statement for it, I think your error will go away.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Why am I seeing a Run-time Error 1004
Replies
5
Views
921
AFPathfinder
A
E
  • Question
With Within With
Replies
2
Views
358
ERLoft
E
E
  • Question
VBA to have user pick option from list.
Replies
3
Views
882
ERLoft
E
P
  • Question
Function Browse Folder Select but I have to Reload twice in subs What am I missing here
Replies
2
Views
428
GTO
G
turtsmurt
  • Question
?Worksheet references without using the sheet "NAME".
Replies
3
Views
1K
turtsmurt
turtsmurt

Forum statistics

Threads
1,216,098
Messages
6,128,812
Members
449,468
Latest member
AGreen17

Share this page

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