Next without For, End If without Block If

[Teleporpoise]

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:

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,

 

[vw412]

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.

 

[vw412]

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?

 

[Fluff]

How about

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

 

[Teleporpoise]

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.

 

[Teleporpoise]

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.

 

[vw412]

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?

 

[Teleporpoise]

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.

 

[Fluff]

Have you tried what I suggested in post#13?

 

[Teleporpoise]

Have you tried what I suggested in post#13?

I did, unfortunately, it still didn't return the data that I want it to return

 

[Fluff]

In what way?