End If without Block If Error Help

  • Thread starter Thread starter Bull
  • Start date Start date
B

Bull

I have this code and keep getting the error, ..I have notated where
the error hits, can someone help me out? Basically, when a value is
selected from a dropdown on a sheet, values are filled in to assigned
cells based on the dropdown selection.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static rOldCell As Range
If Not rOldCell Is Nothing Then
If Not Intersect(rOldCell, Range("I2")) Is Nothing Then
If Range("I2").Value Like "SPOKANE" Then _
Range("C32").Value =
Sheets("ERRORREPORT").Range("B22").Value
Range("E32").Value =
Sheets("ERRORREPORT").Range("C22").Value
Range("G32").Value =
Sheets("ERRORREPORT").Range("D22").Value
Range("I32").Value =
Sheets("ERRORREPORT").Range("E22").Value
Range("K32").Value =
Sheets("ERRORREPORT").Range("F22").Value
Range("M32").Value =
Sheets("ERRORREPORT").Range("G22").Value
Range("O32").Value =
Sheets("ERRORREPORT").Range("H22").Value
Range("Q32").Value =
Sheets("ERRORREPORT").Range("I22").Value
Range("S32").Value =
Sheets("ERRORREPORT").Range("J22").Value
Range("V32").Value =
Sheets("ERRORREPORT").Range("K22").Value
Range("X32").Value =
Sheets("ERRORREPORT").Range("L22").Value
Range("Z32").Value =
Sheets("ERRORREPORT").Range("M22").Value
Range("AB32").Value =
Sheets("ERRORREPORT").Range("N22").Value
Range("AA11").Value =
Sheets("ERRORREPORT").Range("O22").Value
Range("AA12").Value =
Sheets("ERRORREPORT").Range("P22").Value
Range("AA13").Value =
Sheets("ERRORREPORT").Range("Q22").Value
End If
If Range("I2").Value Like "OPPORTUNITY" Then _
Range("C32").Value =
Sheets("ERRORREPORT").Range("B23").Value
Range("E32").Value =
Sheets("ERRORREPORT").Range("C23").Value
Range("G32").Value =
Sheets("ERRORREPORT").Range("D23").Value
Range("I32").Value =
Sheets("ERRORREPORT").Range("E23").Value
Range("K32").Value =
Sheets("ERRORREPORT").Range("F23").Value
Range("M32").Value =
Sheets("ERRORREPORT").Range("G23").Value
Range("O32").Value =
Sheets("ERRORREPORT").Range("H23").Value
Range("Q32").Value =
Sheets("ERRORREPORT").Range("I23").Value
Range("S32").Value =
Sheets("ERRORREPORT").Range("J23").Value
Range("V32").Value =
Sheets("ERRORREPORT").Range("K23").Value
Range("X32").Value =
Sheets("ERRORREPORT").Range("L23").Value
Range("Z32").Value =
Sheets("ERRORREPORT").Range("M23").Value
Range("AB32").Value =
Sheets("ERRORREPORT").Range("N23").Value
Range("AA11").Value =
Sheets("ERRORREPORT").Range("O23").Value
Range("AA12").Value =
Sheets("ERRORREPORT").Range("P23").Value
Range("AA13").Value =
Sheets("ERRORREPORT").Range("Q23").Value
End If
End If<<<<<----Error Hits Here
End If
Set rOldCell = Target
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static rOldCell As Range
If Not rOldCell Is Nothing Then
If Not Intersect(rOldCell, Range("I2")) Is Nothing Then
If Range("I2").Value Like "SPOKANE" Then _
End If
If Range("I2").Value Like "OPPORTUNITY" Then _
End If
End If<<<<<----Error Hits Here
End If
Set rOldCell = Target
End Sub

i took your code & entered it as text in an excel sheet, and then hid
all rows with your value = range stuff.
then to be sure, i color coded the text of each if-then-endif
statement.
i don't see an error!
you've got enough "end if's" to match your "if's". maybe somebody
else can see something.

the only thing i DO see is that because you're not turning off
EnableEvents, it's possible it's triggering the _change over & over
again.

sorry, i tried.
:)
susan
 
the other thing is that in this syntax:

If Range("I2").Value Like "OPPORTUNITY" Then _
End If


you don't need a "_" at the end of Then.
:)
susan
 
Get rid of the underscores after the Then. additionally you can tighten up
the code with a with statement something like this...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static rOldCell As Range

With Sheets("ERRORREPORT")
If Not rOldCell Is Nothing Then
If Not Intersect(rOldCell, Range("I2")) Is Nothing Then
If Range("I2").Value Like "SPOKANE" Then
Range("C32").Value = .Range("B22").Value
Range("E32").Value = .Range("C22").Value
Range("G32").Value = .Range("D22").Value
Range("I32").Value = .Range("E22").Value
Range("K32").Value = .Range("F22").Value
Range("M32").Value = .Range("G22").Value
Range("O32").Value = .Range("H22").Value
Range("Q32").Value = .Range("I22").Value
Range("S32").Value = .Range("J22").Value
Range("V32").Value = .Range("K22").Value
Range("X32").Value = .Range("L22").Value
Range("Z32").Value = .Range("M22").Value
Range("AB32").Value = .Range("N22").Value
Range("AA11").Value = .Range("O22").Value
Range("AA12").Value = .Range("P22").Value
Range("AA13").Value = .Range("Q22").Value
End If
If Range("I2").Value Like "OPPORTUNITY" Then
Range("C32").Value = .Range("B23").Value
Range("E32").Value = .Range("C23").Value
Range("G32").Value = .Range("D23").Value
Range("I32").Value = .Range("E23").Value
Range("K32").Value = .Range("F23").Value
Range("M32").Value = .Range("G23").Value
Range("O32").Value = .Range("H23").Value
Range("Q32").Value = .Range("I23").Value
Range("S32").Value = .Range("J23").Value
Range("V32").Value = .Range("K23").Value
Range("X32").Value = .Range("L23").Value
Range("Z32").Value = .Range("M23").Value
Range("AB32").Value = .Range("N23").Value
Range("AA11").Value = .Range("O23").Value
Range("AA12").Value = .Range("P23").Value
Range("AA13").Value = .Range("Q23").Value
End If
End If
End If
Set rOldCell = Target
End With
End Sub
 
This is selection change code (not change code). Since Bull is not changing
the selection the code will not trigger recursively.
 
Get rid of the underscores after the Then. additionally you can tighten up
the code with a with statement something like this...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static rOldCell As Range

With Sheets("ERRORREPORT")
If Not rOldCell Is Nothing Then
If Not Intersect(rOldCell, Range("I2")) Is Nothing Then
If Range("I2").Value Like "SPOKANE" Then
Range("C32").Value = .Range("B22").Value
Range("E32").Value = .Range("C22").Value
Range("G32").Value = .Range("D22").Value
Range("I32").Value = .Range("E22").Value
Range("K32").Value = .Range("F22").Value
Range("M32").Value = .Range("G22").Value
Range("O32").Value = .Range("H22").Value
Range("Q32").Value = .Range("I22").Value
Range("S32").Value = .Range("J22").Value
Range("V32").Value = .Range("K22").Value
Range("X32").Value = .Range("L22").Value
Range("Z32").Value = .Range("M22").Value
Range("AB32").Value = .Range("N22").Value
Range("AA11").Value = .Range("O22").Value
Range("AA12").Value = .Range("P22").Value
Range("AA13").Value = .Range("Q22").Value
End If
If Range("I2").Value Like "OPPORTUNITY" Then
Range("C32").Value = .Range("B23").Value
Range("E32").Value = .Range("C23").Value
Range("G32").Value = .Range("D23").Value
Range("I32").Value = .Range("E23").Value
Range("K32").Value = .Range("F23").Value
Range("M32").Value = .Range("G23").Value
Range("O32").Value = .Range("H23").Value
Range("Q32").Value = .Range("I23").Value
Range("S32").Value = .Range("J23").Value
Range("V32").Value = .Range("K23").Value
Range("X32").Value = .Range("L23").Value
Range("Z32").Value = .Range("M23").Value
Range("AB32").Value = .Range("N23").Value
Range("AA11").Value = .Range("O23").Value
Range("AA12").Value = .Range("P23").Value
Range("AA13").Value = .Range("Q23").Value
End If
End If
End If
Set rOldCell = Target
End With
End Sub
Click to expand...

thanks everyone...works now
 
This is selection change code (not change code). Since Bull is not changing
the selection the code will not trigger recursively.
Click to expand...

oh. thanks for explaining. :)
susan
 
Back
Top