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,
 
Looking at your code and making some mods. Question, can there be more than 1 row copied to LookUp for each value entered? Asking so I know where to increment LookUpCounter.
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Re-reading your description of what you are working with I think I understand somewhat. Sheet2 can have multiple entries for each employee and you want to show on sheet1 all those lines. Is that correct? For sheet3, is there more than one entry for each employee? If only 1 line per employee, do you want the data from that sheet to show on every line from sheet2? If there is more than one line per employee in sheet3, which line from sheet2 do you want each line from sheet3 to show?
 
Upvote 0
How about
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
        
           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
           End If
       Next i
       For i = 1 To LR
            If 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 i
                      
        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
 
Upvote 0
Solution
Looking at your code and making some mods. Question, can there be more than 1 row copied to LookUp for each value entered? Asking so I know where to increment LookUpCounter.
Yes, there can! The data in sheets 2 and 3 are split by date and shift, therefore "john smith" will show up in multiple rows based on the amount of times he's worked in the past quarter.
 
Upvote 0
Re-reading your description of what you are working with I think I understand somewhat. Sheet2 can have multiple entries for each employee and you want to show on sheet1 all those lines. Is that correct? For sheet3, is there more than one entry for each employee? If only 1 line per employee, do you want the data from that sheet to show on every line from sheet2? If there is more than one line per employee in sheet3, which line from sheet2 do you want each line from sheet3 to show?
You got it, sheet three will also have multiple entries related to the person's name. I want the data from the columns specified in the code.
 
Upvote 0
What I needed to know is: for each line from sheet2 is there a corresponding line from sheet3? Your code seems to want to combine a sheet2 line with a sheet3 line. If that is the case then I will need to change my logic somewhat. If there is a correspondence between sheet2 and sheet3 how do you select the sheet 3 entry that fits with sheet2?
 
Upvote 0
What I needed to know is: for each line from sheet2 is there a corresponding line from sheet3? Your code seems to want to combine a sheet2 line with a sheet3 line. If that is the case then I will need to change my logic somewhat. If there is a correspondence between sheet2 and sheet3 how do you select the sheet 3 entry that fits with sheet2?

The lines will not be identical. For example, row 3 on sheet 2 might have the information on "john smith" but the information on "john smith" is on rows 5, 7, and 29 in sheet 3.
 
Upvote 0
Have you tried what I suggested in post#13?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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