Interesting problem.
Without specifics of your spreadsheet model, I can only talk in generalities.
The first step in the solution is how to identify when a solution exists and when it doesn't. If there is a readily identifiable condition that you could test for after Solver has finished, that might be the easiest.
As an exercise, I made up my own spreadsheet that I could solve with Solver, then recorded a macro that setup the basic code.
VBA help calls "solversolve" a function, so I expected that it should return a value, and hopefully that value would be related to whether or not Solver had converged to a solution. I changed that line of code to
slv1=solversolve(true)'parentheses are needed around arguments when setting to variable
Sure enough, when Solver found a solution, it returned 0, when it couldn't find a feasible solution, it returned 5.
I don't know of anywhere where the return values for the solversolve function are documented, but I expect if you spent some time exploring your specific problem, you would be able to identify what return values represent "no solution" for you specific spreadsheet model. This assumes that Solver recognizes that it didn't come to a solution. I'm also assuming that the result from solversolve will be reasonably consistent from run to run.
Using one of those two approaches, you can then identify the "no solution" condition. Then, simply put NA where you want it to be.
If (no solution exists) then
celltomodify.value=cverr(xlerrna)
end if
The only thing to note in this snippet is that if celltomodify contains a formula, you will lose the formula when you set it to NA. If this is what you want, make sure you leave yourself a way to recover the formula the next time you need it.
Hope that helps.
Bookmarks