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

 

[Alex Blakenburg]

I can't work out what your code is trying to do.
Do you really have a cell in your spreadsheet that has $D$30 in the cell ?
Do you have a range of data at J47 that is 4 columns wide and 25 rows long ?
You don't seem to be doing anything with the resulting array MyCDs.
When do you want the Change event to trigger ? Is it actually when D30 is manually changed ?

Please answer the above an elaborate on what you are trying to do.

 

[nmounir]

I can't work out what your code is trying to do.
Do you really have a cell in your spreadsheet that has $D$30 in the cell ?
Do you have a range of data at J47 that is 4 columns wide and 25 rows long ?
You don't seem to be doing anything with the resulting array MyCDs.
When do you want the Change event to trigger ? Is it actually when D30 is manually changed ?

Please answer the above an elaborate on what you are trying to do.

I have a list of certificate of Deposits (CDs) that will expire in different times. if I select via a drop down list say August 2022, a list of all the CD's that will be expired at that time will show. if I select July 2022, the list will show less CD's expired. Thus, the list expands or contrasts depending on the date selected by the user. This is done by a regular excel formula using the filter function. When the list expands, the user should input the new rate in % that the CD will be renewed at. What I am trying to do is when the lists contrasts / shrinks, the percentage that was previously input when the list was longer, needs to be removed. please see below image 1 and 2.
As per the below image, Cell L45 or D30 are the cells where the date is selected. I am showing Cell L45 for simplicity when taking the snapshot.
you could see now that the list is 5 rows and 4 columns. if a later date is selected, the list can expand to 25 rows.

If the user selects an earlier date say July 2022, the list shrinks to one row only. Since the interest is input manually, then I want the code to remove the hard coded % input in column L. Since the code that I used doesn't fire up, the percentages in column L are still there and were not removed or converted to Zero as per image 2 below.
Please let me know if this is clear or you require further clarity.

 

[Alex Blakenburg]

What cell has the filter function in it and how many columns is it returning ?
Do you want the macro to trigger only when D30 changes ?
You are not showing row numbers but I assume the heading Renewal of Exp CDs resides in J46.

All you want to do is clear the interest rate where there is no data in the equivalent row in column J, please confirm ?
Do you want zero or clear ?
I am not sure I would bother with an array, to you specifically want to use one ?

 

[nmounir]

What cell has the filter function in it and how many columns is it returning ?
Do you want the macro to trigger only when D30 changes ?
You are not showing row numbers but I assume the heading Renewal of Exp CDs resides in J46.

All you want to do is clear the interest rate where there is no data in the equivalent row in column J, please confirm ?
Do you want zero or clear ?
I am not sure I would bother with an array, to you specifically want to use one ?

The cell that has the filter function is Cell J47 that has $3000 (the first amount in the table /list) and I want the Macro to trigger when cell D30 changes (cell D30 has a drop down list of the dates)
Yes all I want to do is clear the interedt rate when there is no data in the equivalent row in column J.
The sheet is super super slow that is why I would prefer using an array. When I write 2% for instance, the cell takes 2 seconds after I press enter to go to the cell below.

 

[Alex Blakenburg]

See if this works for you:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim LastRowCDs As Long, LastRowInt As Long
    
    If Not Intersect(Target, Range("$D$30")) Is Nothing Then
        Application.EnableEvents = False
        LastRowCDs = Range("J" & Rows.Count).End(xlUp).Row
        LastRowInt = Range("L" & Rows.Count).End(xlUp).Row
        Range(Cells(LastRowCDs + 1, "L"), Cells(LastRowInt, "L")).Value = ""   '<---- If you want zero change it to 0
    End If
    Application.EnableEvents = True
End Sub

 

[nmounir]

See if this works for you:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim LastRowCDs As Long, LastRowInt As Long
   
    If Not Intersect(Target, Range("$D$30")) Is Nothing Then
        Application.EnableEvents = False
        LastRowCDs = Range("J" & Rows.Count).End(xlUp).Row
        LastRowInt = Range("L" & Rows.Count).End(xlUp).Row
        Range(Cells(LastRowCDs + 1, "L"), Cells(LastRowInt, "L")).Value = ""   '<---- If you want zero change it to 0
    End If
    Application.EnableEvents = True
End Sub

Thank you very much for this code. since there was no loop, the sheet is not slower.

I made a little tweak to the code. Please see below.

just out of curiosity, any idea why the array didn't work?

 

[bebo021999]

Back to your initial code, I saw this

If target.value = "$L$45"

actually target.value IS NOT L45, then can not fire change

you might need:

If target.address = "$L$45"

to fire change

 

[nmounir]

Back to your initial code, I saw this

If target.value = "$L$45"

actually target.value IS NOT L45, then can not fire change

you might need:

If target.address = "$L$45"

to fire change

It is at "$D$30", i just put L45 to make it easy for me to take the snapshot since this data is confidential. but the actual and real target is in "$D$30" and it was written correctly in the code

 

[bebo021999]

It is at "$D$30", i just put L45 to make it easy for me to take the snapshot since this data is confidential. but the actual and real target is in "$D$30" and it was written correctly in the code

D30 or l45, it does not matter, but the way of comparison does.
target.value is value of target cell
"D30" (or "L45") is target.address
that why i suggest:

If Target.address = "$D$30"

 

[nmounir]

D30 or l45, it does not matter, but the way of comparison does.
target.value is value of target cell
"D30" (or "L45") is target.address
that why i suggest:

If Target.address = "$D$30"

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