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