Excel Macro VBA to try diferent values in some variables and put the results in a table

I need to create an Excel Macro or VBA code to to try diferent values in some variables and put the results in a table. I have an 3 interconnected Excel Books. Two of them are to make intermediate calculations and one of them is the one that make the main calculations. In this last one I have nearly 5 variables that can be combined with diferent allowed values. These combinations produce different results in separated cells, like Profit, and various ratios. Now I have to introduce manually the values and see the results 1 by 1, to find the most aproppiate combination for the most beneficial results. The goal is to automatize this task creating a Code that enter different values in the cells of the different variables and copy the results of Profit, Ratios, etc in an independent table. The VBA Code have to enter the value or values, wait enought time for excel to calculate the results (more or less 10 seconds), and copy these results in the mentioned independent table. Then enter the next value or values, wait again, and copy the results. And repeat this procedure as many times as needed to complete all the variables and values combinations. It is expected to have to perform thousands of combinations. So, the VBA Code has to be designed to run for many hours, due to the time Excel takes to do each calculation. It would also be desirable if the code could clean the Office cache, so that so much garbage does not accumulate that it could collapse the computer's RAM and make Excel stop working.