Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing text to formula in a range of cells
I'm new in Excel having recently moved from another spreadsheet brand that
didn't require the "=" preceding a formula so I'm constantly running into the problem of having to go back and insert the "=" in each formula cell. To do this, I came up with: Sub Equation() With Selection .FormulaR1C1 = "=" & .FormulaR1C1 End With End Sub .... which works perfectly as long as only one cell is selected. If I select a range of cells I get the error message: Run-time error '13': Type mismatch What to do? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing text to formula in a range of cells
How about looping.
Dim rng As Range, cell As Range Set rng = Selection For Each cell In rng.Cells cell.FormulaR1C1 = "=" & cell.Value Next -- Tim Zych www.higherdata.com Compare data in worksheets and find differences with Workbook Compare A free, powerful, flexible Excel utility "Bob Arnett" wrote in message ... I'm new in Excel having recently moved from another spreadsheet brand that didn't require the "=" preceding a formula so I'm constantly running into the problem of having to go back and insert the "=" in each formula cell. To do this, I came up with: Sub Equation() With Selection .FormulaR1C1 = "=" & .FormulaR1C1 End With End Sub ... which works perfectly as long as only one cell is selected. If I select a range of cells I get the error message: Run-time error '13': Type mismatch What to do? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing text to formula in a range of cells
You will want to iterate through the cells in the Selection...
Dim R As Range For Each R In Selection R.FormulaR1C1 = "=" & R.FormulaR1C1 Next Rick "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message ... How about looping. Dim rng As Range, cell As Range Set rng = Selection For Each cell In rng.Cells cell.FormulaR1C1 = "=" & cell.Value Next -- Tim Zych www.higherdata.com Compare data in worksheets and find differences with Workbook Compare A free, powerful, flexible Excel utility "Bob Arnett" wrote in message ... I'm new in Excel having recently moved from another spreadsheet brand that didn't require the "=" preceding a formula so I'm constantly running into the problem of having to go back and insert the "=" in each formula cell. To do this, I came up with: Sub Equation() With Selection .FormulaR1C1 = "=" & .FormulaR1C1 End With End Sub ... which works perfectly as long as only one cell is selected. If I select a range of cells I get the error message: Run-time error '13': Type mismatch What to do? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing text to formula in a range of cells
Sorry... I accidentally sent this post when I actually meant to cancel it.
Rick "Rick Rothstein (MVP - VB)" wrote in message ... You will want to iterate through the cells in the Selection... Dim R As Range For Each R In Selection R.FormulaR1C1 = "=" & R.FormulaR1C1 Next Rick "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message ... How about looping. Dim rng As Range, cell As Range Set rng = Selection For Each cell In rng.Cells cell.FormulaR1C1 = "=" & cell.Value Next -- Tim Zych www.higherdata.com Compare data in worksheets and find differences with Workbook Compare A free, powerful, flexible Excel utility "Bob Arnett" wrote in message ... I'm new in Excel having recently moved from another spreadsheet brand that didn't require the "=" preceding a formula so I'm constantly running into the problem of having to go back and insert the "=" in each formula cell. To do this, I came up with: Sub Equation() With Selection .FormulaR1C1 = "=" & .FormulaR1C1 End With End Sub ... which works perfectly as long as only one cell is selected. If I select a range of cells I get the error message: Run-time error '13': Type mismatch What to do? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing text to formula in a range of cells
Cool! Worked perfectly. I'll get the hang of this yet.
"Tim Zych" wrote: How about looping. Dim rng As Range, cell As Range Set rng = Selection For Each cell In rng.Cells cell.FormulaR1C1 = "=" & cell.Value Next -- Tim Zych www.higherdata.com Compare data in worksheets and find differences with Workbook Compare A free, powerful, flexible Excel utility "Bob Arnett" wrote in message ... I'm new in Excel having recently moved from another spreadsheet brand that didn't require the "=" preceding a formula so I'm constantly running into the problem of having to go back and insert the "=" in each formula cell. To do this, I came up with: Sub Equation() With Selection .FormulaR1C1 = "=" & .FormulaR1C1 End With End Sub ... which works perfectly as long as only one cell is selected. If I select a range of cells I get the error message: Run-time error '13': Type mismatch What to do? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing the range for averages with out changing the formula. | Excel Worksheet Functions | |||
Copy formula into multiple cells without changing range | Excel Worksheet Functions | |||
Pasting a formula in multiple cells without changing the range | Excel Discussion (Misc queries) | |||
monitoring a changing range of cells | Excel Worksheet Functions | |||
Changing color on a range of cells | Excel Programming |