Change event using an Array in VBA

nmounir

Board Regular
Joined
Oct 16, 2020
Messages
89
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,
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 am trying to do here is that if a later date is selected and the interest rates were already manually input, then the user comes and decides to select an earlier date, the list would shrink and so the interest rate that was manually input should then be removed automatically. I am trying to write a code as an array since my sheet is very slow. I wrote this but it is not working and am getting a "Next without for" error despite the fact that i have two For loops in the code

for next image 1.PNG


for next image 2.PNG


Here is the code used

for next image Code.PNG
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,401
Office Version
  1. 2013
Platform
  1. Windows
Or
Replace
VBA Code:
 If Targey.Value = "$L$45" Then
        MyCDs(Dimension1, Dimension2) = Range("J4").Offset(Dimension1, Dimension2).Value
with
VBA Code:
If Targey.Value = "$L$45" Then MyCDs(Dimension1, Dimension2) = Range("J4").Offset(Dimension1, Dimension2).Value

By deleting spaces!!
 

nmounir

Board Regular
Joined
Oct 16, 2020
Messages
89
Office Version
  1. 365
Platform
  1. Windows
Or
Replace
VBA Code:
 If Targey.Value = "$L$45" Then
        MyCDs(Dimension1, Dimension2) = Range("J4").Offset(Dimension1, Dimension2).Value
with
VBA Code:
If Targey.Value = "$L$45" Then MyCDs(Dimension1, Dimension2) = Range("J4").Offset(Dimension1, Dimension2).Value

By deleting spaces!!
I did that and now i am getting a compile error message "End if Without Block if"

for next image Code 1.PNG

Try putting your End If before Next Dimension2
I did this and i am getting an End if without block If error. Please see message above
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,651
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Can you use the vba button and post your code with the change that failed
 

nmounir

Board Regular
Joined
Oct 16, 2020
Messages
89
Office Version
  1. 365
Platform
  1. Windows
Can you use the vba button and post your code with the change that failed
Please see code below

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyCDs(0 To 24, 0 To 3) As Variant
Dim Dimension1 As Long, Dimension2 As Long

Application.EnableEvents = False
For Dimension1 = LBound(MyCDs, 1) To UBound(MyCDs, 1)
For Dimension2 = LBound(MyCDs, 2) To UBound(MyCDs, 2)
If Target.Value = "$D$30" Then MyCDs(Dimension1, Dimension2) = Range("J47").Offset(Dimension1, Dimension2).Value

If MyCDs(Dimension1, 0) = "" Then MyCDs(Dimension1, 2) = 0

End If

Next Dimension2

Next Dimension1

Application.EnableEvents = True

End Sub
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,651
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Get rid of the End If altogether both your If statements seem to have the "Then" action item on the same line

Please use the VBA button when you post code.
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,401
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyCDs(0 To 24, 0 To 3) As Variant
    Dim Dimension1 As Long, Dimension2 As Long
    Application.EnableEvents = False
    For Dimension1 = LBound(MyCDs, 1) To UBound(MyCDs, 1)
        For Dimension2 = LBound(MyCDs, 2) To UBound(MyCDs, 2)
            If Target.Value = "$D$30" Then MyCDs(Dimension1, Dimension2) = Range("J47").Offset(Dimension1, Dimension2).Value
            If MyCDs(Dimension1, 0) = "" Then MyCDs(Dimension1, 2) = 0
        Next
    Next
    Application.EnableEvents = True
End Sub
 

nmounir

Board Regular
Joined
Oct 16, 2020
Messages
89
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyCDs(0 To 24, 0 To 3) As Variant
    Dim Dimension1 As Long, Dimension2 As Long
    Application.EnableEvents = False
    For Dimension1 = LBound(MyCDs, 1) To UBound(MyCDs, 1)
        For Dimension2 = LBound(MyCDs, 2) To UBound(MyCDs, 2)
            If Target.Value = "$D$30" Then MyCDs(Dimension1, Dimension2) = Range("J47").Offset(Dimension1, Dimension2).Value
            If MyCDs(Dimension1, 0) = "" Then MyCDs(Dimension1, 2) = 0
        Next
    Next
    Application.EnableEvents = True
End Sub
I copied and pasted your code but it didn't fire up but no errors is showing. I am not sure how to get it to work?
 

nmounir

Board Regular
Joined
Oct 16, 2020
Messages
89
Office Version
  1. 365
Platform
  1. Windows
Get rid of the End If altogether both your If statements seem to have the "Then" action item on the same line

Please use the VBA button when you post code.
I did this and the error is now gone but the code doesn't fire up
 
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,392
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