Paste Special Values VBA

[bbcdancer]

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

 

[Tom Ogilvy]

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

 

[Dave Peterson]

With cell.Offset(0, 6).Resize(1, 1)
.formluar1c1 = "=RC[-1]+76)"
.value = .value
end with

(similar in both spots)

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

 

[Tom Ogilvy]

"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

With cell.Offset(0, 6).Resize(1, 1)
.formluar1c1 = "=RC[-1]+76)"
.value = .value
end with

(similar in both spots)

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]

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>

"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

With cell.Offset(0, 6).Resize(1, 1)
.formluar1c1 = "=RC[-1]+76)"
.value = .value
end with

(similar in both spots)

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