Private Sub Worksheet_Change(ByVal Target As Range) issue in VBA

[BMD4]

Hello All,

I have excel file where in I have included 2 logics for Private Sub Worksheet_Change(ByVal Target As Range).

In sheet1, we have cell K4 which is ERP and this is drop down. Once we select value from the drop down, it should display message to clear all the details. Once the details are cleared, then we should select values based on the ERP selected which is col J. Col J is again a drop-down which gives source CoCo values for the ERP selected. These source and Target values are in sheet 2.

In my code, I have the logic to clear the values upon ERP selection and to populate target values based on source are in the same block

Private Sub Worksheet_Change(ByVal Target As Range)

Not sure, if this is causing issue in clearing the values when selecting ERPs. Also, while populate target value which is the second section of the code, I am referring to cell K4.

This is just for Coco. I need to replicate the same for other fields like Acct as well. If I include the logic for all the columns in Worksheet_Change and try to select ERP, then the file just hangs.

[dangelor]

Try disabling events in the first part of your code also.

[BMD4]

Hello,

Thanks for quick reply.

I updated the code. Now what I see is, Target values are not getting cleared.

Please find attached the file.

[dangelor]

Possibly...

Please Login or Register  to view this content.

[BMD4]

Hello,

I updated the code, but do not see target values coming.

Also, if we do a copy paste of multiple values, that is also not populating target members.

Attached the file. Please advise.

Thanks in advance.

[dangelor]

Try this...

Please Login or Register  to view this content.

[BMD4]

Thank you. This works for few members.

But, we have users who try to copy 1000+ members in source column. In that scenario, we see target values populating one at a time which is taking time to populate target for 1000+ members.

Is there a way we can improve the performance if we select multiple source members at a time.

Also, we see calculating (8 Threads) running for a very long time and hangs the excel.

Thanks in advance

[dangelor]

Instead of VBA, you might try putting your lookup formula into the cells in column b before you paste.

[BMD4]

Hello,

Please let me know if you are looking for actual formula in excel.

I have initially setup excel formula instead of VBA.

But formulas are taking time to execute as the formula extends for 2000 rows.

So, I am looking to replace excel formula with VBA to improve performance

[dangelor]

Whether the lookup formula is in vba or on the worksheet, it needs to be calculated using worksheet ranges, and that takes time. You might get better results looping through the data using arrays.

[Artik]

Try turning off more than just events for the duration of the calculation:

Please Login or Register  to view this content.

Artik

[BMD4]

Thanks Artik.

I have added the calculation lines to the code. However, did not see much difference.

Can you please help me understand if this reduces calculation time.

[dangelor]

Possibly using a dictionary to capture your data first...

Please Login or Register  to view this content.

[Artik]

I have added the calculation lines to the code. However, did not see much difference.

Can you please help me understand if this reduces calculation time.

After reading about the long running problem, I concluded that it may be due to the large number of formulas in the workbook. Since not much changed after adding the code I suggested, from this I conclude that the cause is not the formulas.
That is, the cause may lie in the method used in the code itself. I will admit that I have tried multiplying the data and inserting 1000 items into column "J", but I did not notice a significant degradation in execution time.

Artik