lundi 3 mai 2021

Next without For, End If without If

I am struggling with my code below. In this version, I get a "next without for error"

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

In this version, I also get the error "next without for"

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

And if I remove both "Next" statements, I get a "End if without block if" as an error... I can't even tell if my code works, I am getting frustrated moving around my "next" and "end if" statements, I can't get it right. Any pointers would be appreciated.

Thank you,

Aucun commentaire:

Enregistrer un commentaire