Next without For, End If without Block If

T

Teleporpoise

New Member
Joined
May 23, 2019
Messages
31
Hi All,

I've moved around my "if" and "end if", as well as the "for" and "next" statements in my code, but I continue to get one of those two errors no matter where I move the statements. Here is my code:


VBA Code: 
Option Explicit

Private Sub worksheet_change(ByVal Target As Range)
    
    Dim Lookup As Worksheet, Data As Worksheet, PF As Worksheet
    Dim LastRow As Long, LR As Long, LookupCounter As Long, i As Long, j As Long
  
    With ThisWorkbook
        Set Lookup = .Worksheets("Lookup")
        Set Data = .Worksheets("Data")
        Set PF = .Worksheets("PF")
    End With
    
    LastRow = Data.Cells(Rows.Count, "A").End(xlUp).Row
    LR = PF.Cells(Rows.Count, "A").End(xlUp).Row
    LookupCounter = 2
    
    For i = 2 To LastRow
    For j = 2 To LR
        

    If Intersect(Lookup.Range("A2"), Target) Is Nothing Then
        Exit Sub
    Else
        ' clear sheet
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Lookup.Range("A2").Value = UCase(Lookup.Range("A2"))
        Lookup.Range("B2:H2000").Clear
        ' get data
        
           If Lookup.Range("A2") = Data.Cells(i, 2) Then
               Lookup.Cells(LookupCounter, 3).Value = Data.Cells(i, 1)
               Lookup.Cells(LookupCounter, 4).Value = Data.Cells(i, 9)
               LookupCounter = LookupCounter + 1
        
            ElseIf Lookup.Range("A2") = PF.Cells(j, 2) Then
                Lookup.Cells(LookupCounter, 6).Value = PF.Cells(j, 1)
                Lookup.Cells(LookupCounter, 7).Value = PF.Cells(j, 12)
                Lookup.Cells(LookupCounter, 8).Value = PF.Cells(j, 10)
                Lookup.Cells(LookupCounter, 9).Value = PF.Cells(j, 2)
                LookupCounter = LookupCounter + 1
        
        Lookup.Range("C2:C2000").NumberFormat = "mm/dd/yyyy"
        Lookup.Range("F2:F2000").NumberFormat = "mm/dd/yyyy"
        Lookup.Range("H2:H2000").Style = "Currency"
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        
    End If
    
    Next
    
End Sub

PF and Data both contain different sets of data for an entry that will be input into the Lookup page.

Let me know what I can do to fix my code.

Thank you,
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
As in your initial post both are missing …​
​
When you create a block statement you must add directly the end codeline for this block​
and just using a correct indentation it helps obviously …​
​
Sample of a valid VBA block code structure well indented so easier to debug when necessary :​
Rich (BB code): 
    For A = … To …
  
        For B = … To …
      
            If … Then
            
            ElseIf … Then
          
                If … Then
                
                ElseIf … Then
                
                End If
              
            End If
          
        Next B
      
    Next A
 
Upvote 0
VBA Code: 
Option Explicit

Private Sub worksheet_change(ByVal Target As Range)
    
    Dim Lookup As Worksheet, Data As Worksheet, PF As Worksheet
    Dim LastRow As Long, LR As Long, LookupCounter As Long, i As Long, j As Long
  
    With ThisWorkbook
        Set Lookup = .Worksheets("Lookup")
        Set Data = .Worksheets("Data")
        Set PF = .Worksheets("PF")
    End With
    
    LastRow = Data.Cells(Rows.Count, "A").End(xlUp).Row
    LR = PF.Cells(Rows.Count, "A").End(xlUp).Row
    LookupCounter = 2

    If Intersect(Lookup.Range("A2"), Target) Is Nothing Then
        Exit Sub
    Else
        ' clear sheet
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Lookup.Range("A2").Value = UCase(Lookup.Range("A2"))
        Lookup.Range("B2:H2000").Clear
        
        ' get data
        For i = 2 To LastRow
        For j = 2 To LR
        
           If Lookup.Range("A2") = Data.Cells(i, 2) Then
               Lookup.Cells(LookupCounter, 3).Value = Data.Cells(i, 1)
               Lookup.Cells(LookupCounter, 4).Value = Data.Cells(i, 9)
               LookupCounter = LookupCounter + 1
        
            ElseIf Lookup.Range("A2") = PF.Cells(j, 2) Then
                Lookup.Cells(LookupCounter, 6).Value = PF.Cells(j, 1)
                Lookup.Cells(LookupCounter, 7).Value = PF.Cells(j, 12)
                Lookup.Cells(LookupCounter, 8).Value = PF.Cells(j, 10)
                Lookup.Cells(LookupCounter, 9).Value = PF.Cells(j, 2)
                LookupCounter = LookupCounter + 1
                
            End If
        Next
                      
        Lookup.Range("C2:C2000").NumberFormat = "mm/dd/yyyy"
        Lookup.Range("F2:F2000").NumberFormat = "mm/dd/yyyy"
        Lookup.Range("H2:H2000").Style = "Currency"
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        
    End If
    
End Sub

I am still receiving an error for "End if without block if"
 
Upvote 0
VBA Code: 
Option Explicit

Private Sub worksheet_change(ByVal Target As Range)
    
    Dim Lookup As Worksheet, Data As Worksheet, PF As Worksheet
    Dim LastRow As Long, LR As Long, LookupCounter As Long, i As Long, j As Long
  
    With ThisWorkbook
        Set Lookup = .Worksheets("Lookup")
        Set Data = .Worksheets("Data")
        Set PF = .Worksheets("PF")
    End With
    
    LastRow = Data.Cells(Rows.Count, "A").End(xlUp).Row
    LR = PF.Cells(Rows.Count, "A").End(xlUp).Row
    LookupCounter = 2

    If Intersect(Lookup.Range("A2"), Target) Is Nothing Then
        Exit Sub
    Else
        ' clear sheet
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Lookup.Range("A2").Value = UCase(Lookup.Range("A2"))
        Lookup.Range("B2:H2000").Clear
        
        ' get data
        For i = 2 To LastRow
        For j = 2 To LR
        
           If Lookup.Range("A2") = Data.Cells(i, 2) Then
               Lookup.Cells(LookupCounter, 3).Value = Data.Cells(i, 1)
               Lookup.Cells(LookupCounter, 4).Value = Data.Cells(i, 9)
               LookupCounter = LookupCounter + 1
        
            ElseIf Lookup.Range("A2") = PF.Cells(j, 2) Then
                Lookup.Cells(LookupCounter, 6).Value = PF.Cells(j, 1)
                Lookup.Cells(LookupCounter, 7).Value = PF.Cells(j, 12)
                Lookup.Cells(LookupCounter, 8).Value = PF.Cells(j, 10)
                Lookup.Cells(LookupCounter, 9).Value = PF.Cells(j, 2)
                LookupCounter = LookupCounter + 1
                
            End If
        Next
        Next
                      
        Lookup.Range("C2:C2000").NumberFormat = "mm/dd/yyyy"
        Lookup.Range("F2:F2000").NumberFormat = "mm/dd/yyyy"
        Lookup.Range("H2:H2000").Style = "Currency"
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        
    End If
    
End Sub

I'm no longer getting the next and end if errors, but the code is not behaving the way I was hoping it would either. In sheet 2 of my workbook, I have names of employees, the dates they came into work, the shifts they worked, and absenteeism. In sheet 3, I have more information related to the workers. In sheet 1 of my code, I have a lookup sheet where I intend for the employee's name to be typed into a cell and to show all the dates and this person worked, along with the shift and absenteeism, and the data from sheet 3, into the lookup sheet. Right now it is just inputting data from sheet two, and it is not a match to the input in sheet 1. What is going on?
 
Upvote 0
If you get rid of the 2nd loop & the elseif like
VBA Code: 
Private Sub worksheet_change(ByVal Target As Range)
    
    Dim Lookup As Worksheet, Data As Worksheet, PF As Worksheet
    Dim LastRow As Long, LR As Long, LookupCounter As Long, i As Long, j As Long
  
    With ThisWorkbook
        Set Lookup = .Worksheets("Lookup")
        Set Data = .Worksheets("Data")
        Set PF = .Worksheets("PF")
    End With
    
    LastRow = Data.Cells(Rows.Count, "A").End(xlUp).Row
    LR = PF.Cells(Rows.Count, "A").End(xlUp).Row
    LookupCounter = 2

    If Intersect(Lookup.Range("A2"), Target) Is Nothing Then
        Exit Sub
    Else
        ' clear sheet
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Lookup.Range("A2").Value = UCase(Lookup.Range("A2"))
        Lookup.Range("B2:H2000").Clear
        
        ' get data
        For i = 2 To LastRow
'        For j = 2 To LR
        
           If Lookup.Range("A2") = Data.Cells(i, 2) Then
               Lookup.Cells(LookupCounter, 3).Value = Data.Cells(i, 1)
               Lookup.Cells(LookupCounter, 4).Value = Data.Cells(i, 9)
               LookupCounter = LookupCounter + 1
        
'            ElseIf Lookup.Range("A2") = PF.Cells(j, 2) Then
'                Lookup.Cells(LookupCounter, 6).Value = PF.Cells(j, 1)
'                Lookup.Cells(LookupCounter, 7).Value = PF.Cells(j, 12)
'                Lookup.Cells(LookupCounter, 8).Value = PF.Cells(j, 10)
'                Lookup.Cells(LookupCounter, 9).Value = PF.Cells(j, 2)
'                LookupCounter = LookupCounter + 1
'
            End If
'        Next
        Next
                      
        Lookup.Range("C2:C2000").NumberFormat = "mm/dd/yyyy"
        Lookup.Range("F2:F2000").NumberFormat = "mm/dd/yyyy"
        Lookup.Range("H2:H2000").Style = "Currency"
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        
    End If
    
End Sub
do you get the correct values from the Data sheet?
 
Upvote 0
So initially, my code looked like this:

VBA Code: 
Option Explicit

Private Sub worksheet_change(ByVal Target As Range)
    
    Dim Lookup As Worksheet, Data As Worksheet
    Dim LastRow As Long, LookupCounter As Long, i As Long
  
    With ThisWorkbook
        Set Lookup = .Worksheets("Lookup")
        Set Data = .Worksheets("Data")
    End With

    If Intersect(Lookup.Range("A1:A2"), Target) Is Nothing Then
        Exit Sub
    Else
        ' clear sheet
        Lookup.Range("B2:C2000").Delete
        LastRow = Data.Cells(Rows.Count, "A").End(xlUp).Row
        LookupCounter = 2
        
        ' get data
        For i = 2 To LastRow
           If Data.Cells(i, 2) = Lookup.Range("A2") Then
               Lookup.Cells(LookupCounter, 2).Value = Data.Cells(i, 1)
               Lookup.Cells(LookupCounter, 3).Value = Data.Cells(i, 9)
               LookupCounter = LookupCounter + 1
           End If
        Next
        Lookup.Range("B2:B2000").NumberFormat = "mm/dd/yyyy"
    End If
        
End Sub

And it worked like a dream. It was only when I introduced the third sheet (PF), that the issues started. When I am looking at (Data) sheet only, the code (the one I just posted) gives me perfect results. But Once I introduced (PF), I am getting the wrong returns for both (Data) and (PF) sheets.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Need help with VBA code prompting Compile error: Next without For
2
Replies
15
Views
498
Pi_Lover
P
S
  • Solved
Copy values only without specific column formats VBA
Replies
9
Views
406
sofas
S
T
  • Solved
Block If without End If
2
Replies
13
Views
950
kevin9999
K
N
  • Solved
how to automatically clear text if text in another cell is removed
Replies
1
Views
147
Joe4
Joe4
A
  • Solved
Could not set the RowSource property. Invalid property value. Error 380
2
Replies
16
Views
267
RoryA
RoryA

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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