0

I would like to find a solution for a question I have.

The user is asked to write a calculation formula in a cell containing either variable names (part of earlier user input) or the cell references for those input variable names. Later these variables or cell references should be replaced by specially created random numbers and the formula should be solved to the result value. How do I read the formula and find the cell references or variable names? And how do I finally handle the formula from the cell in the vba code?

Thanks!

JFS
  • 2,992
  • 3
  • 37
  • 48
  • Are you needing to write a user defined function to achieve this? You cannot modify a cell called from a cell without using a complex workaround like this: http://stackoverflow.com/questions/12501759/vba-update-other-cells-via-user-defined-function – CodeJockey May 28 '14 at 19:22
  • Thanks CodeJockey, I'm not sure if I got you right but I need to read the formula as user input, find the cell references in the formula (or the variable names) and calculate the formula later on via VBA with numbers based on the references. I don't want to change cell content I just need to know where to read cell content based on the formula. – JFS May 28 '14 at 19:41

1 Answers1

1

If I understand correctly, your sub is going to read the formula in a cell, read data from the cell referenced, and then replace the first cell with a hard coded result? Range.Formula will return the formula as a string instead of the result. You can then parse the formula to get the reference cell, go get the data and overwrite from there. As long as the caller is a macro and not another cell, overwriting the first cell won't be an issue. Here's a quick sample:

Set myRg = Range("A2")
Set RefCell = Range(Left(myRg.Formula, 2))

This assumes that the first two characters in myRg.Formula are the reference cell in question, (e.g. if the formula were "=B2 + C3"). I'm sure you'll need a much more sophisticated way to strip out the reference, but this is a start.

CodeJockey
  • 1,922
  • 1
  • 15
  • 20