covert only cell references in selected cells into value leaving the existingcell formula

  • Thread starter Thread starter al007
  • Start date Start date
A

al007

How can I covert only cell references in selected cells into value
leaving the existingcell formula.e.g
If A1=10 & A2=20 D1=30 & D2=40

A3= A1+A2
D3= D1+D2

I would like to have this

A3 = 10+20
D3=30+40

Thxs
 
Select the cells with the formula, and run the macro below. It will change most cell references to
values, with the exception of multi-cell ranges. That would be do-able, but would require a
re-write.

HTH,
Bernie
MS Excel MVP

Sub Convert()
'Converts cell references to values within the
'Activecell's formula
'Written by Bernie Deitrick Dec 15, 2005

Dim strForm As String
Dim strOrig As String
Dim Addr As Variant
Dim i As Integer
Dim myCell As Range
Const Operators As String = "=+-*/^()"

strForm = ActiveCell.Formula
strOrig = ActiveCell.Formula

For i = 1 To Len(Operators)
strForm = Replace(strForm, Mid(Operators, i, 1), "*")
Next i

Addr = Split(strForm, "*")
For i = LBound(Addr) To UBound(Addr)
On Error GoTo NotCell
Set myCell = Range(Addr(i))
strOrig = Replace(strOrig, Addr(i), myCell.Value)
NotCell:
Resume GoOn
GoOn:
Next i

ActiveCell.Formula = strOrig
End Sub
 
BIG THXS

Bernie said:
Select the cells with the formula, and run the macro below. It will change most cell references to
values, with the exception of multi-cell ranges. That would be do-able, but would require a
re-write.

HTH,
Bernie
MS Excel MVP

Sub Convert()
'Converts cell references to values within the
'Activecell's formula
'Written by Bernie Deitrick Dec 15, 2005

Dim strForm As String
Dim strOrig As String
Dim Addr As Variant
Dim i As Integer
Dim myCell As Range
Const Operators As String = "=+-*/^()"

strForm = ActiveCell.Formula
strOrig = ActiveCell.Formula

For i = 1 To Len(Operators)
strForm = Replace(strForm, Mid(Operators, i, 1), "*")
Next i

Addr = Split(strForm, "*")
For i = LBound(Addr) To UBound(Addr)
On Error GoTo NotCell
Set myCell = Range(Addr(i))
strOrig = Replace(strOrig, Addr(i), myCell.Value)
NotCell:
Resume GoOn
GoOn:
Next i

ActiveCell.Formula = strOrig
End Sub
Click to expand...
 
Can you make it do-able for either a selection of contiguous cells or
non contiguous cells as I would like to have it in my personal macro
collection.
thxs
 
Sub Convert2()
'Converts cell references to values within the
'Selected cell's formulas
'Re-Written by Bernie Deitrick Jan 17, 2005

Dim strForm As String
Dim strOrig As String
Dim Addr As Variant
Dim i As Integer
Dim myCell As Range
Dim mySel As Range
Const Operators As String = "=+-*/^()"

For Each mySel In Selection
strForm = mySel.Formula
strOrig = mySel.Formula

For i = 1 To Len(Operators)
strForm = Replace(strForm, Mid(Operators, i, 1), "*")
Next i

Addr = Split(strForm, "*")
For i = LBound(Addr) To UBound(Addr)
On Error GoTo NotCell
Set myCell = Range(Addr(i))
strOrig = Replace(strOrig, Addr(i), myCell.Value)
NotCell:
Resume GoOn
GoOn:
Next i

mySel.Formula = strOrig
Next mySel
End Sub


HTH,
Bernie
MS Excel MVP
 
thxs a lot

Bernie said:
Sub Convert2()
'Converts cell references to values within the
'Selected cell's formulas
'Re-Written by Bernie Deitrick Jan 17, 2005

Dim strForm As String
Dim strOrig As String
Dim Addr As Variant
Dim i As Integer
Dim myCell As Range
Dim mySel As Range
Const Operators As String = "=+-*/^()"

For Each mySel In Selection
strForm = mySel.Formula
strOrig = mySel.Formula

For i = 1 To Len(Operators)
strForm = Replace(strForm, Mid(Operators, i, 1), "*")
Next i

Addr = Split(strForm, "*")
For i = LBound(Addr) To UBound(Addr)
On Error GoTo NotCell
Set myCell = Range(Addr(i))
strOrig = Replace(strOrig, Addr(i), myCell.Value)
NotCell:
Resume GoOn
GoOn:
Next i

mySel.Formula = strOrig
Next mySel
End Sub


HTH,
Bernie
MS Excel MVP
Click to expand...
 
Bernie,
the revised code is working great except if a referenced cell is empty
- is it possible for you to put value = zero for any empty referenced
cell?

thxs
al
 
Change

strOrig = Replace(strOrig, Addr(i), myCell.Value)

to

strOrig = Replace(strOrig, Addr(i), IIf(myCell.Value = "", "0",
myCell.Value))

HTH,
Bernie
MS Excel MVP
 
thxs again ...........
Bernie said:
Change

strOrig = Replace(strOrig, Addr(i), myCell.Value)

to

strOrig = Replace(strOrig, Addr(i), IIf(myCell.Value = "", "0",
myCell.Value))

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Bernie,
For a long time I've been trying to split the cell ref in a formula
cell into individual cells. I'm thinking of using the first part of
your code & adding the other part which would split text to column
using delimited & spliting the cell as per below:


I think I'm on the right track - but I need your help.

Thxs
aL
 
aL,

I'm not sure what you mean. Post an example of "before and after".

HTH,
Bernie
MS Excel MVP
 
Eg
If cell A1 from sheet 1 = Sheet2!D9+Sheet3!D3-Sheet4!B6
The macro would split A1 as:
B1 = Sheet2!D9
C1 = Sheet3!D3
D1 = -Sheet4!B6

the above is a simple example & should also provide for more complex
situation with * & /
& () like below

If cell A1 from sheet 1 = (Sheet2!D9+Sheet3!D3-Sheet4!B6)*2
The macro would split A1 as:
B1 = Sheet2!D9
C1 = Sheet3!D3
D1 = -Sheet4!B6
E1 = 2


is this do-able?
 
Al,

Try this version.

I hope that "007" doesn't mean you have a license to kill.... ;-)

HTH,
Bernie
MS Excel MVP


Sub Convert3()
'Converts cell references to values within the
'Selected cell's formulas
'and spreads out cell references to the right
'of the cell with the formula
'Re-Written by Bernie Deitrick Jan 19, 2005

Dim strForm As String
Dim strOrig As String
Dim Addr As Variant
Dim Use() As Boolean
Dim i As Integer
Dim j As Integer
Dim myCell As Range
Dim mySel As Range
Dim myNum As Double
Dim KeepSource As Boolean

Const Operators As String = "=+-*/^()"

If MsgBox("Keep original formula intact?", vbYesNo) = vbYes Then
KeepSource = True
End If

For Each mySel In Selection
strForm = mySel.Formula
strOrig = mySel.Formula

For i = 1 To Len(Operators)
strForm = Replace(strForm, Mid(Operators, i, 1), "*")
Next i

Addr = Split(strForm, "*")
ReDim Use(LBound(Addr) To UBound(Addr))

For i = LBound(Addr) To UBound(Addr)
On Error GoTo NotNum:
myNum = CDbl(Addr(i))
Use(i) = True
NotNum:
Resume GoOn1
GoOn1:
Next i

For i = LBound(Addr) To UBound(Addr)
On Error GoTo NotCell
Set myCell = Range(Addr(i))
strOrig = Replace(strOrig, Addr(i), _
IIf(myCell.Value = "", "0", myCell.Value))
Use(i) = True
NotCell:
Resume GoOn2
GoOn2:
Next i

j = 1
For i = LBound(Addr) To UBound(Addr)
If Use(i) Then
mySel(1, j + 1).Formula = "=" & Addr(i)
j = j + 1
End If
Next i

If Not KeepSource Then mySel.Formula = strOrig

Next mySel
End Sub
 
Hi Bernie,
You are the best - & so kind in helping us
Your code is working great - but can you improve it to cater for more
operation possibilities - eg formula involving ranges e.g a cell
containing Vlookup(A1,Sheet1!a:b,2,false) +
Vlookup(a2,Sheet2!a:b,2,false) would be split individually with each
vlookup in a separate cell or other situation = sum(a1:b1) +a1 -
Vlookup(A1,Sheet1!a:b,2,false) would be split.
Hope u won't be sick with all my requests/proposal & keep replying to
me...
Thxs ..take care & long life .... am only a pleasant guy looking for an
excel expert friend !!! not a killer...
 
Al,

Sorry, but that is getting too complex: differentiating between the parens of SUM(A1:B1) and 2*(A1 +
B1), for example, is a problem.

You would need to write a separate macro for each different case: splitting on +, splitting on -,
etc...

You'll have to find another Excel expert to do that.....

Bernie
MS Excel MVP
 
Hi Bernie,
Do you have a macro which cnan change this formulacontaining sum
=SUM('1999'!A1:A2,'2000'!A1,'2001amended'!A1:B1)

into: = :'1999'!A1+'1999'!A2+'2000'!A1+2001amended'!A1+2001amended'!B1

reason being I like to use the summation icon instead of the + sign

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

copy multiple columns 1
Worksheet program to convert parent/children data to string(s) 1
Send multiple cell values to multiple cells 2
Copy Cells Q 3
Un-Delimiting 2
offset cell position when there is value in a selected cell 6
Excel Concatenate with line breaks but ignore blank cells 3
copy formula across while skipping columns 3

Back
Top