End If without Block If Error Help

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
 
S

Susan

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
 
S

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
 
J

Jim Thomlinson

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
 
J

Jim Thomlinson

This is selection change code (not change code). Since Bull is not changing
the selection the code will not trigger recursively.
 
B

Bull

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
 
S

Susan

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Error Message HELP!!!!! 0
I need this to copy only the range that has "Yes" 1
Need help with Error 'object variable or with block variable not set' 5
Compile error: End If without block IF 1
end if without block if error 4
Run-Time error '-2147417848 (80010108)': Automation error The object invoked has disconnected from 0
Run-Time error '-2147417848 (80010108)': Automation error The object invoked has disconnected from 0
End if without Block If 7

Top