Teatro

02-20-2017, 11:48 AM

Is it Possible to Run Goal Seek on array elements within VBA, instead of on an Excel sheet range?

I have a Sub that makes an array which takes initial values from an excel input sheet, does many calculations, and prints values back into a report region on an excel sheet; the output is roughly 200 rows x 28 columns of calculated values. I used to do this with formulas in every cell on the sheet, but it was of course very, very slow. I converted to an all-vba Sub that does the calculations quickly and pastes the finished values into the report range in excel.

The problem is that I also need to run Goal Seek on various cells from time to time in order to make use of the model and Excel can't do it on a sheet cell that just has a value in it, it needs a cell with a formula. With my fancy, efficient array, I can't goal seek anymore because the cells are just numbers pasted in by my Sub!!!!!

Is there a way to run some version of Goal Seek NOT on excel sheet ranges but on array members, like on MyArray(107,23) by testing an input value that is actually on the excel sheet, like Range("B2")? What would that code look like? The ultimate goal would be to call a macro just like calling Goal Seek, enter ranges for Set Cell, To Value, and By Changing Cell just like Excel Goal Seek does, and have it run the operation on MyArray, but any help at all would be most welcome.

I have a Sub that makes an array which takes initial values from an excel input sheet, does many calculations, and prints values back into a report region on an excel sheet; the output is roughly 200 rows x 28 columns of calculated values. I used to do this with formulas in every cell on the sheet, but it was of course very, very slow. I converted to an all-vba Sub that does the calculations quickly and pastes the finished values into the report range in excel.

The problem is that I also need to run Goal Seek on various cells from time to time in order to make use of the model and Excel can't do it on a sheet cell that just has a value in it, it needs a cell with a formula. With my fancy, efficient array, I can't goal seek anymore because the cells are just numbers pasted in by my Sub!!!!!

Is there a way to run some version of Goal Seek NOT on excel sheet ranges but on array members, like on MyArray(107,23) by testing an input value that is actually on the excel sheet, like Range("B2")? What would that code look like? The ultimate goal would be to call a macro just like calling Goal Seek, enter ranges for Set Cell, To Value, and By Changing Cell just like Excel Goal Seek does, and have it run the operation on MyArray, but any help at all would be most welcome.