End if without Block if followed by Block if without End if !

[nmounir]

Good day,

I am trying to write this code but i keep getting this error all the time.

any idea how to fix this?

Thank you

 

[nmounir]

Sub Changeevent()

Dim MyRenewedCDs As Range
Dim CD As Range
Application.EnableEvents = False

Set MyRenewedCDs = Range("J47:J71")
' If Not Intersect(Target, MyRenewedCDs) Is Nothing Then
For Each CD In MyRenewedCDs
If CD.Value = "" Then CD.Offset(0, 3).Value = 0

Next CD
End If

Application.EnableEvents = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRenewedCDs As Range
Dim CD As Range
Application.EnableEvents = False

Set MyRenewedCDs = Range("J47:J71")
' If Not Intersect(Target, MyRenewedCDs) Is Nothing Then
For Each CD In MyRenewedCDs
If CD.Value = "" Then CD.Offset(0, 3).Value = 0

Next CD
End If

Application.EnableEvents = True

End Sub

 

[nmounir]

Keep the code exactly as you have it in post#15.

here is the code exactly as it is in post 15. A variable not defined error pops up because it cannot identify what target is since it is a module not an event.

Sub Changeevent()

Dim MyRenewedCDs As Range
Dim CD As Range
Application.EnableEvents = False

Set MyRenewedCDs = Range("J47:J71")
If Not Intersect(Target, MyRenewedCDs) Is Nothing Then
For Each CD In MyRenewedCDs
If CD.Value = "" Then CD.Offset(0, 3).Value = 0

Next CD
End If

Application.EnableEvents = True

End Sub

 

[Joe4]

Sub Changeevent()

Dim MyRenewedCDs As Range
Dim CD As Range
Application.EnableEvents = False

Set MyRenewedCDs = Range("J47:J71")
' If Not Intersect(Target, MyRenewedCDs) Is Nothing Then
For Each CD In MyRenewedCDs
If CD.Value = "" Then CD.Offset(0, 3).Value = 0

Next CD
End If

Application.EnableEvents = True

End Sub

Why do you have an "End If" for an "If" statement that you commented out?
Either comment BOTH the green "If' and green "End IF" out, or uncomment BOTH out.

Also, this code will never autorun, at least not with a name like "Changevent".
Worksheet Change event procedures code MUST have the first line look EXACTLY like:

Private Sub Worksheet_Change(ByVal Target As Range)

Anything else, and it won't run automatically.

 

[nmounir]

Why do you have an "End If" for an "If" statement that you commented out?
Either comment BOTH the green "If' and green "End IF" out, or uncomment BOTH out.

Also, this code will never autorun, at least not with a name like "Changevent".
Worksheet Change event procedures code MUST have the first line look EXACTLY like:

Private Sub Worksheet_Change(ByVal Target As Range)

Anything else, and it won't run automatically.

it is written correctly as a change event.
the sub was a request from Fluff.

please a picture of the whole code.

 

[Joe4]

That code in your last post is valid code.

If it is not running automatically, I suspect you encountered an error halfway through on one of your runs, thus disabling events and never re-enabling them.
Until they are re-enabled, the event procedure code will not run automatically.

You can easily do that by manually running this one line code:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

Then automated code can run again.

 

[nmounir]

That code in your last post is valid code.

If it is not running automatically, I suspect you encountered an error halfway through on one of your runs, thus disabling events and never re-enabling them.
Until they are re-enabled, the event procedure code will not run automatically.

You can easily do that by manually running this one line code:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

Then automated code can run again.

I did create a module to enable event and and ran it. still doesn't fire up

 

[Joe4]

I did create a module to enable event and and ran it. still doesn't fire up

No, you do not place Event Procedure in created modules. They only work when placed in one of the existing Sheet or ThisWorkbook modules.
Since this is a Worksheet_Change event procedure, it MUST be placed in the Sheet module for the sheet you want to apply it to.
Put it anywhere else, and it won't fire.

One way to make sure that you have put it in the correct place is to go to the sheet you want to run it on, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the code in the VB Editor window that pops up (this is the Sheet module for that sheet).

 

[nmounir]

I did create a module to enable event and and ran it. still doesn't fire up

please see image.

No, you do not place Event Procedure in created modules. They only work when placed in one of the existing Sheet or ThisWorkbook modules.
Since this is a Worksheet_Change event procedure, it MUST be placed in the Sheet module for the sheet you want to apply it to.
Put it anywhere else, and it won't fire.

One way to make sure that you have put it in the correct place is to go to the sheet you want to run it on, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the code in the VB Editor window that pops up (this is the Sheet module for that sheet).

It is posted as am event procedure and not posted as a module. I choose from the top worksheet and selected change event. This is not a module .
I created this as a module only once when fluff (another member here on Mr Excel message board) asked me to try.
If you see this it was only done once for fluff.

 

[Joe4]

Can you tell me the exact thing you are doing that should kick off the code?
What is the address of the cell you are updating?
What value are you updating it to?

 

[nmounir]

Can you tell me the exact thing you are doing that should kick off the code?
What is the address of the cell you are updating?
What value are you updating it to?

I have a list of CDs that have maturity dates. when i select a certain date in the future, the table lists down the CD's that will be expired by then. This is done by an excel formula. that said, this list can expand or contrast according to the date selected (date is selected via a drop down list in cell L45). Once the list populates, the user should manually input (hard coding) the new interest rate in percentage (Column L) at which this CD will be renewed at. What i was trying to do here is that if a later date is selected and the interest rates were already manually input, then the user decides to select an earlier date, the list would shrink and so the interest rate that was manually input should then be removed automatically. Please see image 1 and 2 to fully understand what i am trying to do. you will notice in image 2 that the hard coded interest rate in % was removed because the code fired up after I added the End if after the Next CD as per Fluff recommendations.
The sheet is very slow after using this code. I am trying to write this code as an array to speed it up but I am not sure if i will be successful at writing it
correctly.