Hi,
I need help if this is possible in Excel???
I need VBA to build a value using the "value" of a cell that the formula is refering to.
For example:
In A1 is the value 10
In A2 is the value 20
In A3 is the value 30
In A4 is the value 60 (The formula =A1+A2+A3)
In B4 I need VBA macro to insert: 10+20+30. (This is the value of the cell reference in the formula!)
The reason for the request: Someone wants a visual reference of the formula in the cell next to the formula.
PS: The code should work by looping through the selection of formulas, and insert the visual formula in the cell to the right.
Mnay thanks!
Use VBA to build values from a formula
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Use VBA to build values from a formula
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Use VBA to build values from a formula
Try this:
If you prefer to omit the = from the formula:
Code: Select all
Sub ListFormulas()
Dim rng As Range
For Each rng In Selection.SpecialCells(xlCellTypeFormulas)
rng.Offset(0, 1).Value = Chr(39) & rng.Formula
Next rng
End Sub
Code: Select all
Sub ListFormulas()
Dim rng As Range
For Each rng In Selection.SpecialCells(xlCellTypeFormulas)
rng.Offset(0, 1).Value = Chr(39) & Mid(rng.Formula, 2)
Next rng
End Sub
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Use VBA to build values from a formula
Hi Hans,
Wow...that was quick! :)
It is putting a value next to the formula, but its adding the actual formula =A1+A2+A3. I need 10+20+30. So the code must insert the value from the reference, not the reference itself.
TX
Wow...that was quick! :)
It is putting a value next to the formula, but its adding the actual formula =A1+A2+A3. I need 10+20+30. So the code must insert the value from the reference, not the reference itself.
TX
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Use VBA to build values from a formula
But what if the formula is =SUM(A1:A1000)? Taking all kinds of formulas into account is an impossible task.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Use VBA to build values from a formula
I must agree... I did not think about these scenarios!
Just out of interest (assuming the above sample formula)...Is it possible to pull the value from a reference in a formula? If it is,how would one do it?
Thanks
Just out of interest (assuming the above sample formula)...Is it possible to pull the value from a reference in a formula? If it is,how would one do it?
Thanks
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Use VBA to build values from a formula
If you only have formulas of the type in your example:
I'm not going to expand it to handle -, *, / or ^. You'll have to do that yourself, or wait for someone else to do that for you.
Code: Select all
Sub ListFormulaValues()
Dim rng As Range
Dim strFormula As String
Dim strReturn As String
Dim strCells() As String
Dim i As Integer
For Each rng In Selection.SpecialCells(xlCellTypeFormulas)
strFormula = Mid(rng.Formula, 2)
strCells = Split(strFormula, "+")
strReturn = ""
For i = LBound(strCells) To UBound(strCells)
strReturn = strReturn & "+" & Range(strCells(i)).Value
Next i
rng.Offset(0, 1).Value = "'" & Mid(strReturn, 2)
Next rng
End Sub
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Use VBA to build values from a formula
Hi RudiRudi wrote:Hi Hans,
... the code must insert the value from the reference, not the reference itself.
TX
See if the attached approach will satisfy the needs of your user.
You do not have the required permissions to view the files attached to this post.
Regards
Don
Don
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Use VBA to build values from a formula
Hans,
Thanks for the code. All I was interested in was how one could get the value from the reference. Your code is a perfect explanation for this. If I need it expanded, (which I doubt I will), I am sure I can take it from here! Many TX!!!
Don,
Thanks for this interesting inset! This is a very interesting way of "visualising" the formula. I could present this as a possible solution for the user. Great idea and thanks again. I appreciate the effort again for both of you!
Eileens lounge rocks!!!!!!!!
Cheers
Thanks for the code. All I was interested in was how one could get the value from the reference. Your code is a perfect explanation for this. If I need it expanded, (which I doubt I will), I am sure I can take it from here! Many TX!!!
Don,
Thanks for this interesting inset! This is a very interesting way of "visualising" the formula. I could present this as a possible solution for the user. Great idea and thanks again. I appreciate the effort again for both of you!
Eileens lounge rocks!!!!!!!!
Cheers
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.