Use formula from cell in UserForm?
 

I'm trying to create a UserForm that will detect which row my
ActiveCell is in, then go to certain columns in that row and pull in
the formulas for those columns. The formulas are all pretty much
"=(CellRef)+/- (SomeValue)" - nothing exotic at all. I then want to
substitue a value manually typed into the Form for the cell reference
in the formula and display the result in the Form.

So if the formula in D4 is "=B4-456", the UserForm code would pull in
that formula, substitute an inputted number for B4, and display the
result of the formula in a text box or label.

My questions a
-- is there a built-in function that recognizes a formula in a cell
and lets you use it as such in VBA code?
-- is there an easy way to identify the cell reference and substitute
another variable?

Ed

Use formula from cell in UserForm?
 

Sub Complicated()
Dim objRef As Range
Dim objCell As Range
Dim FormulaCell As Range

Set FormulaCell = ActiveCell
On Error Resume Next
Set objRef = FormulaCell.Precedents
On Error GoTo 0
If Not objRef Is Nothing Then
For Each objCell In objRef
MsgBox objCell.Value
Next
End If
Set objRef = Nothing
Set objCell = Nothing
Set FormulaCell = Nothing
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Ed from AZ"
wrote in message
I'm trying to create a UserForm that will detect which row my
ActiveCell is in, then go to certain columns in that row and pull in
the formulas for those columns. The formulas are all pretty much
"=(CellRef)+/- (SomeValue)" - nothing exotic at all. I then want to
substitue a value manually typed into the Form for the cell reference
in the formula and display the result in the Form.

So if the formula in D4 is "=B4-456", the UserForm code would pull in
that formula, substitute an inputted number for B4, and display the
result of the formula in a text box or label.

My questions a
-- is there a built-in function that recognizes a formula in a cell
and lets you use it as such in VBA code?
-- is there an easy way to identify the cell reference and substitute
another variable?

Ed

Use formula from cell in UserForm?
 

Thanks, Jim! I appreciate the boost.

Ed

On Jun 28, 4:42 pm, "Jim Cone" wrote:
Sub Complicated()
Dim objRef As Range
Dim objCell As Range
Dim FormulaCell As Range

Set FormulaCell = ActiveCell
On Error Resume Next
Set objRef = FormulaCell.Precedents
On Error GoTo 0
If Not objRef Is Nothing Then
For Each objCell In objRef
MsgBox objCell.Value
Next
End If
Set objRef = Nothing
Set objCell = Nothing
Set FormulaCell = Nothing
End Sub
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware

"Ed from AZ"
wrote in message
I'm trying to create a UserForm that will detect which row my
ActiveCell is in, then go to certain columns in that row and pull in
the formulas for those columns. The formulas are all pretty much
"=(CellRef)+/- (SomeValue)" - nothing exotic at all. I then want to
substitue a value manually typed into the Form for the cell reference
in the formula and display the result in the Form.

So if the formula in D4 is "=B4-456", the UserForm code would pull in
that formula, substitute an inputted number for B4, and display the
result of the formula in a text box or label.

My questions a
-- is there a built-in function that recognizes a formula in a cell
and lets you use it as such in VBA code?
-- is there an easy way to identify the cell reference and substitute
another variable?

Ed