Memory Error - Need help to optimize VBA code

I am using VBA to find and replace currency formats across all worksheets based on currency selection in a particular cell. The code works just fine functionally, but I often run into an error - "There is not enough memory to complete this operation. Try using less data or closing other applications."

I am new to VBA and I believe this is because my code isn't optimal in its usage of memory. Below is the code for the whole function. The yellow highlighted area is where the VBA stops executing and throws the memory error. I will be very thankful for a solution!


Update - I have attached a sample workbook with the current code and the objective explained within.

Please note that while this sample workbook only handles 2 currencies in 2 worksheets, I need this code to optimally work over 5 data-intensive tabs with 10+ currencies

Problem - In the actual workbook, I often run into a memory error as described previously.

Request Assistance in - Please help me optimize the code in this attached example workbook to utilize less memory so that the memory error does not show up in my actual workbook.

Please Login or Register  to view this content.

Hi

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

It's not clear why you're using a UDF rather than a standard Procedure, and cycling through every cell in a loop is almost certainly not the most optimum way.

Just running a simple test, these lines error:

Please Login or Register  to view this content.

That might just be because I don't have appropriate date and/or formatting.

There are other, simpler ways of referencing the Named Ranges. Some comparisons:

Please Login or Register  to view this content.

If you upload a sample file, we can test your code in its own environment.

Thanks a lot for your responses.


I have attached a sample workbook with the current code and the objective explained within.

Please note that while this sample workbook only handles 2 currencies in 2 worksheets, I need this code to optimally work over 5 data-intensive tabs with 10+ currencies

Problem - In the actual workbook, I often run into a memory error as described previously.

Request Assistance in - Please help me optimize the code in this attached example workbook to utilize less memory so that the memory error does not show up in my actual workbook.


Thanks a lot again!