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

 

[Joe4]

The thing to remember when working with multi-line blocks in VBA (like "If/End If", "With/End If", "For/Next"), the LAST one started must be the FIRST one to close (and likewise, the FIRST one started must be the LAST one to close).

If you indent your code properly, it make it very easy to see and follow along, i.e.

With Range(...)
    For ...
        If ...

        End If
    Next
End With

 

[Fluff]

i tried to put the end if before and after and both methods didn't work !!!

Post the code when you tried what I suggested & show the error you get.

 

[nmounir]

As I said before it must go after the Next Cd line

I did write it after the Next CD and it didn't give me any error but the code didn't work. the code didn't fire up

 

[nmounir]

The thing to remember when working with multi-line blocks in VBA (like "If/End If", "With/End If", "For/Next"), the LAST one started must be the FIRST one to close (and likewise, the FIRST one started must be the LAST one to close).

If you indent your code properly, it make it very easy to see and follow along, i.e.

With Range(...)
    For ...
        If ...

        End If
    Next
End With

I am a VBA Beginner so i may not able to ident it correctly

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]

Post the code when you tried what I suggested & show the error you get.

here is the code with the way you suggested. i didn't get any error but the code didn't fire up. as you can see the 2% remains while it should have replaced by Zero

		Jul-2022​
Renewal of Exp CD's	New Purchase Date	Interest Rate	Interest Amount
$3,000​	4-Jun-22	2%​	$5​
		2%​	​
		2%​	​
		2%​	​

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]

I am a VBA Beginner so i may not able to ident it correctly

Forget the indenting for a moment (that just makes it easier to visualize).
Note what I said in my previous post, and take a look at the pattern (I color-coded it to make it obvious).
If you understand that, then it will become evident exactly what you need to do to fix your code.

 

[Fluff]

Put this code in a normal module & run it. Then with the code you posted in post#15 try changing a value in J47:J71

 

[nmounir]

Put this code in a normal module & run it. Then with the code you posted in post#15 try changing a value in J47:J71

I am getting the same error in a normal module.

 

[Fluff]

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

 

[nmounir]

Forget the indenting for a moment (that just makes it easier to visualize).
Note what I said in my previous post, and take a look at the pattern (I color-coded it to make it obvious).
If you understand that, then it will become evident exactly what you need to do to fix your code.

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