Change event using an Array in VBA

nmounir

Board Regular
Joined
Oct 16, 2020
Messages
107
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 a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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!!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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
Back
Top