Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special Values VBA
The peice of code below works fine. However, I need the formulas after
calculating the answer to become paste special values. Example: " =24+56" is a formula answer of 80 ...Which is what I currently get. What I want in the cell is 80. Is there a easy way to do this in the below code? Many thanks... Sub semiannual() Dim v As Variant Dim v1 As Variant Dim rng As Range Dim cell As Range Dim res As Variant v1 = Array("S") Set rng = Range(Cells(3, 4), Cells(3, 4).End(xlDown)) For Each cell In rng res = Application.Match(cell.Value, v1, 0) If Not IsError(res) Then cell.Offset(0, 3).Resize(1, 1).Value = "=RC[-5]+56 " 'Should paste special values here. cell.Offset(0, 6).Resize(1, 1).Value = "=RC[-1]+76)" 'Should paste special values here. End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special Values VBA
With cell.Offset(0, 3).Resize(1, 1)
.FormulaR1C1 = "=RC[-5]+56 " .Formula = .Value End with 'Should paste special values here. With cell.Offset(0, 6).Resize(1, 1) .FormulaR1C1 = "=RC[-1]+76" .Formula = .Value End With -- Regards, Tom Ogilvy wrote in message ups.com... The peice of code below works fine. However, I need the formulas after calculating the answer to become paste special values. Example: " =24+56" is a formula answer of 80 ...Which is what I currently get. What I want in the cell is 80. Is there a easy way to do this in the below code? Many thanks... Sub semiannual() Dim v As Variant Dim v1 As Variant Dim rng As Range Dim cell As Range Dim res As Variant v1 = Array("S") Set rng = Range(Cells(3, 4), Cells(3, 4).End(xlDown)) For Each cell In rng res = Application.Match(cell.Value, v1, 0) If Not IsError(res) Then cell.Offset(0, 3).Resize(1, 1).Value = "=RC[-5]+56 " 'Should paste special values here. cell.Offset(0, 6).Resize(1, 1).Value = "=RC[-1]+76)" 'Should paste special values here. End If Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special Values VBA
"Oopsie"!!
.formluar1c1 = "=RC[-1]+76)" probably want to change the spelling of "formlua" to "formula" and drop the extra paren on the end of the function. .formular1c1 = "=RC[-1]+76" -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... With cell.Offset(0, 6).Resize(1, 1) .formluar1c1 = "=RC[-1]+76)" .value = .value end with (similar in both spots) wrote: The peice of code below works fine. However, I need the formulas after calculating the answer to become paste special values. Example: " =24+56" is a formula answer of 80 ...Which is what I currently get. What I want in the cell is 80. Is there a easy way to do this in the below code? Many thanks... Sub semiannual() Dim v As Variant Dim v1 As Variant Dim rng As Range Dim cell As Range Dim res As Variant v1 = Array("S") Set rng = Range(Cells(3, 4), Cells(3, 4).End(xlDown)) For Each cell In rng res = Application.Match(cell.Value, v1, 0) If Not IsError(res) Then cell.Offset(0, 3).Resize(1, 1).Value = "=RC[-5]+56 " 'Should paste special values here. cell.Offset(0, 6).Resize(1, 1).Value = "=RC[-1]+76)" 'Should paste special values here. End If Next End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Special Values VBA
I'll take full responsibility for the FormulaR1c1 debacle!
But only partial for the parenthesis--I copied and pasted. (I didn't notice, so I didn't correct.) But other than that, it was a fine post! <vbg Tom Ogilvy wrote: "Oopsie"!! .formluar1c1 = "=RC[-1]+76)" probably want to change the spelling of "formlua" to "formula" and drop the extra paren on the end of the function. .formular1c1 = "=RC[-1]+76" -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... With cell.Offset(0, 6).Resize(1, 1) .formluar1c1 = "=RC[-1]+76)" .value = .value end with (similar in both spots) wrote: The peice of code below works fine. However, I need the formulas after calculating the answer to become paste special values. Example: " =24+56" is a formula answer of 80 ...Which is what I currently get. What I want in the cell is 80. Is there a easy way to do this in the below code? Many thanks... Sub semiannual() Dim v As Variant Dim v1 As Variant Dim rng As Range Dim cell As Range Dim res As Variant v1 = Array("S") Set rng = Range(Cells(3, 4), Cells(3, 4).End(xlDown)) For Each cell In rng res = Application.Match(cell.Value, v1, 0) If Not IsError(res) Then cell.Offset(0, 3).Resize(1, 1).Value = "=RC[-5]+56 " 'Should paste special values here. cell.Offset(0, 6).Resize(1, 1).Value = "=RC[-1]+76)" 'Should paste special values here. End If Next End Sub -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste Special - Values | Excel Worksheet Functions | |||
paste special values | Excel Worksheet Functions | |||
Paste Special Values | Excel Programming | |||
paste special values | Excel Worksheet Functions | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming |