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

nmounir

Board Regular
Joined
Oct 16, 2020
Messages
89
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • end if without block if.PNG
    end if without block if.PNG
    21.7 KB · Views: 10
  • End if without block if 2.PNG
    End if without block if 2.PNG
    21.4 KB · Views: 10

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,711
Office Version
  1. 2013
Platform
  1. Windows
It's always best to post all the code here and not a image of the code.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,297
Office Version
  1. 365
Platform
  1. Windows
You should only have one End If & it should go after the Next CD line
 
Solution

nmounir

Board Regular
Joined
Oct 16, 2020
Messages
89
Office Version
  1. 365
Platform
  1. Windows
I put End If after the Next CD line and i am still getting the same error.
I also tried to write code as an array to make the sheet faster since it is already very slow but it is not working.
Basically what i am trying to do is if i choose a certain date in some other cell (say L44), the table would contract (using formula) and i would like to the percentage in the third row (which hard coded) to become zero as per image 2 and 3
 

Attachments

  • Capture.PNG
    Capture.PNG
    31.7 KB · Views: 3
  • Table 1.PNG
    Table 1.PNG
    6.7 KB · Views: 3
  • Table 2.PNG
    Table 2.PNG
    4.4 KB · Views: 3

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,315
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The "End If" needs to come before the "Next CD".
 

nmounir

Board Regular
Joined
Oct 16, 2020
Messages
89
Office Version
  1. 365
Platform
  1. Windows
I apologise i posted a wrong code in my second post. i tried to put the end if before and after and both methods didn't work !!!
as could see the 2% in column 3 in the table didn't change to 0

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, 2).Value = 0
End If
Next CD

Application.EnableEvents = True

End Sub
 

Attachments

  • Table 3.PNG
    Table 3.PNG
    33.7 KB · Views: 3
  • Table 4.PNG
    Table 4.PNG
    4.8 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,297
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I put End If after the Next CD line and i am still getting the same error.
That's because that code is totally different from the code you originally posted.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,297
Office Version
  1. 365
Platform
  1. Windows
As I said before it must go after the Next Cd line
 

nmounir

Board Regular
Joined
Oct 16, 2020
Messages
89
Office Version
  1. 365
Platform
  1. Windows
That's because that code is totally different from the code you originally posted.
Yes i apologise for this mistake i fixed it on my third post.
 

nmounir

Board Regular
Joined
Oct 16, 2020
Messages
89
Office Version
  1. 365
Platform
  1. Windows
I apologise i posted a wrong code in my second post. i tried to put the end if before and after and both methods didn't work !!!
as could see the 2% in column 3 in the table didn't change to 0

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, 2).Value = 0
End If
Next CD

Application.EnableEvents = True

End Sub
 

Attachments

  • Table 3.PNG
    Table 3.PNG
    33.7 KB · Views: 2
  • Table 4.PNG
    Table 4.PNG
    4.8 KB · Views: 2
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,282
Messages
5,836,388
Members
430,425
Latest member
xlsee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top