Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leaving a formula cell blank when data has not yet been added. | Excel Discussion (Misc queries) | |||
Convert selected formula references from relative to absolute | Excel Discussion (Misc queries) | |||
Copying a formula while leaving one cell fixed | Excel Discussion (Misc queries) | |||
Leaving a total sum cell blank until other cells are filled | Excel Worksheet Functions | |||
Copy cells to neighbor column after leaving the cell | Excel Worksheet Functions |