Loading data from user form to cell is very slow

[vinodhmmraj]

Hi Team

I have this code and it makes my worksheet load slowly. Data from user form is being populated into a row very slowly. I have the code added to this post.

File has 10 tabs with many Vlookup from one workbook to other. Kindly help me to make the loading process faster

code as follows

Please Login or Register  to view this content.

[vinodhmmraj]

can anyone help me on this????????

[AliGW]

Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. Members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

From the forum rules (link in the main menu above):

• If your question has not been answered within a day, consider adding another post with any additional information you believe is relevant. If you think your post is good as is, just reply to your own thread with the words "Bump no response", which will bring it to the top of the forum.

[mjr veverka]

... They live in many different time zones ...

blackat.gif

1. ... this code ... makes my worksheet load slowly. Data ... is being populated into a row very slowly...
2. ... File has 10 tabs with many Vlookup from one workbook to other...

It's not a macro, the main culprit are the functions on the sheet, take a look here for example, a similar topic:
https://www.excelforum.com/excel-for...umproduct.html

When it comes to macros, then:
1. Don't use the "MsgBox" function in a user-defined function, rather send the result of the UDF to "MsgBox".

2. "Application.ScreenUpdating" is unnecessary in the function, because the function is passive, it does not perform active tasks.

3. Don't use words reserved in vba as variables, e.g. 'Name' in 'FindColumnByName' function.

4. What in fact does the 'RowBOMLevel' function do (btw, missing 'c' variable) ?
a. The 'For ... Next' loop in this UDF will only be executed once for the 'If' statement, and then no longer, because of 'LevelVal = "" ' before the loop.
b. 'LevelVal = Cells(r, c).Text' - Is the formatted value really required ? After a while you're converting it to 'Integer': 'Level = CInt(LevelVal)', so all you need is '.Value'.

5. 'Range("AA1:AA5000").Column' in 'CommandButton1_Click' is always 27 and 'Range("AB1:AB5000").Column' is always 28.

6. 'vbNullString' in 'Worksheet_Change':
The meaning of 'vbNullString' => "String having value 0, not the same as a zero-length string (""); used for calling external procedures." - from contextual help 'Ms'.
Are you sure it's not about ' "" ' ?
If "InputBox("Reason for Changes:", "Add Comments...")" is "empty", then 'UserComments' = "".

7. The 'FindColumnByName' function can be written with a simpler construction without loop, e.g.:

Please Login or Register  to view this content.

It should be a bit faster.

[vinodhmmraj]

hi porucha vevrku
I will use your valuable suggestion to update and will let u know how it works,

Thanks

[vinodhmmraj]

Hi Porucha vevrku
Need your help on this
1/ Don't use the "MsgBox" function in a user-defined function, rather send the result of the UDF to "MsgBox". (can you use my code and let me know how to do it.)

Please Login or Register  to view this content.

5. 'Range("AA1:AA5000").Column' in 'CommandButton1_Click' is always 27 and 'Range("AB1:AB5000").Column' is always 28. - can you explain me this what i need to do

[mjr veverka]

Ad 5. For example:

Please Login or Register  to view this content.

[vinodhmmraj]

Hi Porucha vevrku
i replaced my code with your and it seems it take more time than before. Inserting blank row takes more time than before and after i feed in the data in user form it takes approx 1 min 20 sec to insert into its respective cells.

Can you kindly help me on this please.

Thanks
Vinodh

[mjr veverka]

And in this way (change of 'UserForm' generation places, at the end of the macro) ?

Please Login or Register  to view this content.

[vinodhmmraj]

Hi Porucha vevrku
i replaced my code with your and it seems it reduced 15 sec time than before. but still in the 1min range. any more guidance possible

Can you kindly help me on this please.

Thanks
Vinodh

[mjr veverka]

1. How do you measure this time ?
2. How do you fill 'UserForm' ?
3. Have you already solved the case of the "huge amount" of the 'VLookup' function in your workbook ?

[vinodhmmraj]

Hi Porucha Vevrku

1- I stop watch the time i click the submit button
2- User form (screenshot of user form attached) is manually filled in by user and click enter data button
3- Huge data in other worksheet using vlookup still not resolved. need some help there as well. if you could help me that would be great

Thanks
Vinodh

[mjr veverka]

Get acquainted with this topic (the main thesis: "Wherever possible, replace the functions with their results"; functions can always be reproduced, manually or with a macro):
https://www.excelforum.com/excel-for...umproduct.html

and/or look for others, similar on forum, e.g:
https://www.excelforum.com/excel-pro...readsheet.html
https://www.excelforum.com/excel-for...culations.html

others, e.g.:
https://msdn.microsoft.com/en-us/vba...on-performance