Change event using an Array in VBA

[nmounir]

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

Here is the code used

 

[nmounir]

In the array Macro I wrote it "$D$30". Is that OK?

 

[bebo021999]

So how would u like me to write it in the code? I am wondering why the Macro Array doesn't fire up

Just correct it first, to see if the issue solved.

 

[bebo021999]

In the array Macro I wrote it "$D$30". Is that OK?

That's OK with "Target.address = "$D$30"", not Target.value

 

[nmounir]

That's OK with "Target.address = "$D$30"", not Target.value

I changed it to Target.Address instead of Target.Value and same issue persists. Code doesn't fire up

 

[Alex Blakenburg]

I changed it to Target.Address instead of Target.Value and same issue persists. Code doesn't fire up

I wouldn't use an array for what you are doing but the below might give you some ideas on why your wasn't working.
Some key points
1) Your spreadsheet was running very slowly because your check for whether you wanted to run the code was "inside" the loop.
(ie Target.Address = $D$30). It should be the first thing the change event does so it doesn't run anything else if it fails to qualify.
By having it inside the loops it will cycle through all the code even though D30 didn't change.

2) You are not loading MyCDs before entering the loop. It is again inside the loop and using the loop variables so that it is never actually just loading the array starting values.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim MyCDs As Variant
    Dim Dimension1 As Long
    Dim LastRowCDs As Long, LastRowInt As Long
    
    If Target.Address = "$D$30" Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        LastRowCDs = Range("J" & Rows.Count).End(xlUp).Row
        LastRowInt = Range("L" & Rows.Count).End(xlUp).Row
        MyCDs = Range(Cells(47, "J"), Cells(Application.Max(LastRowCDs, LastRowInt), "M")).Value
        
        For Dimension1 = LBound(MyCDs, 1) To UBound(MyCDs, 1)
                If MyCDs(Dimension1, 1) = "" Then MyCDs(Dimension1, 3) = 0
        Next Dimension1
        
        Range("J47").Offset(0, 2).Resize(UBound(MyCDs, 1)).Value = Application.Index(MyCDs, 0, 3)
        
    End If
    
    Application.EnableEvents = True
    Application.EnableEvents = True

End Sub

 

[nmounir]

I wouldn't use an array for what you are doing but the below might give you some ideas on why your wasn't working.
Some key points
1) Your spreadsheet was running very slowly because your check for whether you wanted to run the code was "inside" the loop.
(ie Target.Address = $D$30). It should be the first thing the change event does so it doesn't run anything else if it fails to qualify.
By having it inside the loops it will cycle through all the code even though D30 didn't change.

2) You are not loading MyCDs before entering the loop. It is again inside the loop and using the loop variables so that it is never actually just loading the array starting values.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim MyCDs As Variant
    Dim Dimension1 As Long
    Dim LastRowCDs As Long, LastRowInt As Long
   
    If Target.Address = "$D$30" Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        LastRowCDs = Range("J" & Rows.Count).End(xlUp).Row
        LastRowInt = Range("L" & Rows.Count).End(xlUp).Row
        MyCDs = Range(Cells(47, "J"), Cells(Application.Max(LastRowCDs, LastRowInt), "M")).Value
       
        For Dimension1 = LBound(MyCDs, 1) To UBound(MyCDs, 1)
                If MyCDs(Dimension1, 1) = "" Then MyCDs(Dimension1, 3) = 0
        Next Dimension1
       
        Range("J47").Offset(0, 2).Resize(UBound(MyCDs, 1)).Value = Application.Index(MyCDs, 0, 3)
       
    End If
   
    Application.EnableEvents = True
    Application.EnableEvents = True

End Sub

This is absolutely brilliant, it works very fast and i feel the difference in the speed of the sheet. I now understand what mistakes I made when building this array; you explained it very well. I should load the array first before looping and the target should be placed first and never have it the loop.
Thanks again Alex for your patience and help with this. Much appreciated.

 

[Alex Blakenburg]

Glad we were able to help. I appreciate your feedback.