ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   End If without Block If Error Help (https://www.excelbanter.com/excel-programming/388419-end-if-without-block-if-error-help.html)

Bull
End If without Block If Error Help
 
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

Susan
End If without Block If Error Help
 
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



On Apr 30, 2:11 pm, Bull wrote:
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

Susan
End If without Block If Error Help
 
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

Jim Thomlinson
End If without Block If Error Help
 
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
--
HTH...

Jim Thomlinson


"Bull" wrote:

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

Jim Thomlinson
End If without Block If Error Help
 
This is selection change code (not change code). Since Bull is not changing
the selection the code will not trigger recursively.
--
HTH...

Jim Thomlinson


"Susan" wrote:

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



On Apr 30, 2:11 pm, Bull wrote:
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

Bull
End If without Block If Error Help
 
On Apr 30, 11:34 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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
--
HTH...

Jim Thomlinson

"Bull" wrote:
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

thanks everyone...works now

Susan
End If without Block If Error Help
 
On Apr 30, 2:38 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
This is selection change code (not change code). Since Bull is not changing
the selection the code will not trigger recursively.
--
HTH...

Jim Thomlinson


oh. thanks for explaining. :)
susan


All times are GMT +1. The time now is 03:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com