Next without For, End If without Block If

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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

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