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

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

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

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



"al007" wrote in message
oups.com...
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

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

BIG THXS

Bernie Deitrick wrote:
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



"al007" wrote in message
oups.com...
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

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

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

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

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


"al007" wrote in message
oups.com...
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

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

thxs a lot

Bernie Deitrick wrote:
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


"al007" wrote in message
oups.com...
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

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

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


Bernie Deitrick wrote:
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


"al007" wrote in message
oups.com...
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

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

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




"al007" wrote in message
oups.com...
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


Bernie Deitrick wrote:
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


"al007" wrote in message
oups.com...
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

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

thxs again ...........
Bernie Deitrick wrote:
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




"al007" wrote in message
oups.com...
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


Bernie Deitrick wrote:
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


"al007" wrote in message
oups.com...
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

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

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:


Sub Split()


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
' need additional code which would split the cell - delimited & split at all @ - so that I have all referenced cells in individual cells next to the cell containing the formula.

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

Thxs
aL














Bernie Deitrick wrote:
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




"al007" wrote in message
oups.com...
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


Bernie Deitrick wrote:
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


"al007" wrote in message
oups.com...
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

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

aL,

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

HTH,
Bernie
MS Excel MVP


"al007" wrote in message
oups.com...
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:


Sub Split()


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
' need additional code which would split the cell - delimited & split at all @ - so that I
have all referenced cells in individual cells next to the cell containing the formula.

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

Thxs
aL

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

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?



Bernie Deitrick wrote:
aL,

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

HTH,
Bernie
MS Excel MVP


"al007" wrote in message
oups.com...
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:


Sub Split()


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
' need additional code which would split the cell - delimited & split at all @ - so that I
have all referenced cells in individual cells next to the cell containing the formula.

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

Thxs
aL

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

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




"al007" wrote in message
oups.com...
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?

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

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...





Bernie Deitrick wrote:
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




"al007" wrote in message
oups.com...
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?

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

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


"al007" wrote in message
oups.com...
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...





Bernie Deitrick wrote:
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




"al007" wrote in message
oups.com...
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?

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

it's ok - thxs again for your help .. !!!!
take care until my next post



Bernie Deitrick wrote:
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


"al007" wrote in message
oups.com...
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...





Bernie Deitrick wrote:
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




"al007" wrote in message
oups.com...
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?

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

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+200 1amended'!B1

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

Thanks








Bernie Deitrick wrote:
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


"al007" wrote in message
oups.com...
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...





Bernie Deitrick wrote:
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




"al007" wrote in message
oups.com...
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?