Hi,
I would like to be able to select one group of cells (Group 1) and a second Group of Cells (Cell 2) and then have Visual Basic automatically write the cell equation and write a corresponding string above it.
The cells may not be a continuous range. I've attached a sample excel file in which this operation has "already been done"
The reason I'm asking such a broad question is that I'm not even sure if it's possible. I thought one of you might know.
Thanks in advance.
Its possible, but why? The formula bar has very similar functionality.
Are you looking to create the string "b+d+e-d-e-f" and then have that evaluated in the cell below as you change the values in column B.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Basically I have a lot of equations that need to be set up to be solved using Solver, which is why preserving the cell reference is necessary.
I could select each cell one at a time but I'd have to go back and manually type in the reference string above the calculation cell.
If there were some way to just click the positive value cells, and then the negative value cells an enormous amount of time could be easily saved. I'm also very curious to know if it can be done using Vba, it would helpful with other projects as well.
==edit===
So I figured out a way I might be able to do this. I could usewhere
Please Login or Register to view this content.. I can also use the same method to compile the label. The only problem I have now is figuring out a way to define F through a GUI. I would prefer to have a method where I can bring up two lists in a user box, one for the addition and one for the subtraction. If there is another way though I would love to hear it!Please Login or Register to view this content.
Last edited by canyon289; 10-08-2011 at 09:53 PM.
Rather than this kludge for entering the formula, you could extract the cells from the formula to give it to Solver.
How do I do this? Sorry for all the questions I'm pretty new to VBA programming
Rather than a formula entering userform, you could enter the formula normaly and use this UDF to extract the formula that you wanted.
FromulaReplace has optional RowOffset and ColumnOffset arguments.
It looks into a cell, reads the formula, and replace the cell references with (offset) values from those cells.
In the attached, =ReplaceFormula(D9, 0, -1)
takes =B3+B5+B7-(B2-B4-B6)
and returns "= b + d + f -( a - c - e )"
If you in VBA and looking for the precedents of a cell's formula, Range("D9").Precedents will return those cells as a range.Please Login or Register to view this content.
Last edited by mikerickson; 10-09-2011 at 12:58 AM.
Wow,
I would have never come up with that on my own. Thank you very very much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks