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:
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,
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,