Macro to Edit Contents of Cell
 

I'm pulling in a formula to a cell based on the contents of another sheet.
Since Excel converts that formula to text, I'm writing a macro to copy from
one cell and paste values to another.

Example: One cell has =VLOOKUP(D:D,AZInputs,9). This give me a formula like
=(C:C)*(F:F) because that is the contents of column 9 on the named range
AZInputs.

My macro will copy this looked up formula from one cell to another and paste
values so that instead of seeing the lookup =VLOOKUP(D:D,AZInputs,9) in the
cell contents, I see the pasted formula, =(C:C)*(F:F).

So far, so good. I don't have a problem up to here. But the pasted formula
is text instead of a true formula so even on the spread sheet I see
=(C:C)*(F:F) instead of the results of that formula (2x4=8). If I select
this cell, click the F2 button (edit), and then just hit enter, the cell
realizes it's a formula and gives me the results.

Finally my question: How can write a macro that would be the equivelant of
clicking F2 and hitting enter. When I click record macro, I get the results
below, which aren't generic enough.

Sub junk()
ActiveCell.FormulaR1C1 = "=(C:C)*(F:F)"
Range("F4").Select
End Sub

Macro to Edit Contents of Cell
 

First, your formula is not in R1C1 Reference style--it's in A1 reference style.

Sub junk()
with activecell
.numberformat = "General" 'not text!
.Formula = .value
end with
End Sub

But I bet it would be quicker to
select the range
format it as General
and then do
Edit|Replace
what: = (equal sign)
with: =
replace all

You could record a macro if you need to.

TKS_Mark wrote:

I'm pulling in a formula to a cell based on the contents of another sheet.
Since Excel converts that formula to text, I'm writing a macro to copy from
one cell and paste values to another.

Example: One cell has =VLOOKUP(D:D,AZInputs,9). This give me a formula like
=(C:C)*(F:F) because that is the contents of column 9 on the named range
AZInputs.

My macro will copy this looked up formula from one cell to another and paste
values so that instead of seeing the lookup =VLOOKUP(D:D,AZInputs,9) in the
cell contents, I see the pasted formula, =(C:C)*(F:F).

So far, so good. I don't have a problem up to here. But the pasted formula
is text instead of a true formula so even on the spread sheet I see
=(C:C)*(F:F) instead of the results of that formula (2x4=8). If I select
this cell, click the F2 button (edit), and then just hit enter, the cell
realizes it's a formula and gives me the results.

Finally my question: How can write a macro that would be the equivelant of
clicking F2 and hitting enter. When I click record macro, I get the results
below, which aren't generic enough.

Sub junk()
ActiveCell.FormulaR1C1 = "=(C:C)*(F:F)"
Range("F4").Select
End Sub

--

Dave Peterson

Macro to Edit Contents of Cell
 

Dave Peterson,
That was a big help. Now, what if I select multiple rows? I changed the
formula as below, but instead of row, I want something like rows.
With ActiveSheet
With .Cells(ActiveCell.Row, "S")
.Copy
End With
With .Cells(ActiveCell.Row, "F")
.PasteSpecial Paste:=xlPasteValues
End With
With .Cells(ActiveCell.Row, "F").Resize(1, 2)
.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
End With


"Dave Peterson" wrote:

First, your formula is not in R1C1 Reference style--it's in A1 reference style.

Sub junk()
with activecell
.numberformat = "General" 'not text!
.Formula = .value
end with
End Sub

But I bet it would be quicker to
select the range
format it as General
and then do
Edit|Replace
what: = (equal sign)
with: =
replace all

You could record a macro if you need to.

TKS_Mark wrote:

I'm pulling in a formula to a cell based on the contents of another sheet.
Since Excel converts that formula to text, I'm writing a macro to copy from
one cell and paste values to another.

Example: One cell has =VLOOKUP(D:D,AZInputs,9). This give me a formula like
=(C:C)*(F:F) because that is the contents of column 9 on the named range
AZInputs.

My macro will copy this looked up formula from one cell to another and paste
values so that instead of seeing the lookup =VLOOKUP(D:D,AZInputs,9) in the
cell contents, I see the pasted formula, =(C:C)*(F:F).

So far, so good. I don't have a problem up to here. But the pasted formula
is text instead of a true formula so even on the spread sheet I see
=(C:C)*(F:F) instead of the results of that formula (2x4=8). If I select
this cell, click the F2 button (edit), and then just hit enter, the cell
realizes it's a formula and gives me the results.

Finally my question: How can write a macro that would be the equivelant of
clicking F2 and hitting enter. When I click record macro, I get the results
below, which aren't generic enough.

Sub junk()
ActiveCell.FormulaR1C1 = "=(C:C)*(F:F)"
Range("F4").Select
End Sub

--

Dave Peterson

Macro to Edit Contents of Cell
 

Maybe...

Option Explicit
Sub testme()

Dim myRng As Range

'single area at a time
Set myRng = Selection.Areas(1)

With Intersect(myRng.EntireRow, ActiveSheet.Range("s1").EntireColumn)
.Copy
With .Offset(0, -13)
.PasteSpecial Paste:=xlPasteValues
With .Resize(, 2) 'same number of rows, but two columns
.Replace What:="=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With
End With
End With

End Sub

Still fixing columns F:G, right?

TKS_Mark wrote:

Dave Peterson,
That was a big help. Now, what if I select multiple rows? I changed the
formula as below, but instead of row, I want something like rows.
With ActiveSheet
With .Cells(ActiveCell.Row, "S")
.Copy
End With
With .Cells(ActiveCell.Row, "F")
.PasteSpecial Paste:=xlPasteValues
End With
With .Cells(ActiveCell.Row, "F").Resize(1, 2)
.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
End With

"Dave Peterson" wrote:

First, your formula is not in R1C1 Reference style--it's in A1 reference style.

Sub junk()
with activecell
.numberformat = "General" 'not text!
.Formula = .value
end with
End Sub

But I bet it would be quicker to
select the range
format it as General
and then do
Edit|Replace
what: = (equal sign)
with: =
replace all

You could record a macro if you need to.

TKS_Mark wrote:

I'm pulling in a formula to a cell based on the contents of another sheet.
Since Excel converts that formula to text, I'm writing a macro to copy from
one cell and paste values to another.

Example: One cell has =VLOOKUP(D:D,AZInputs,9). This give me a formula like
=(C:C)*(F:F) because that is the contents of column 9 on the named range
AZInputs.

My macro will copy this looked up formula from one cell to another and paste
values so that instead of seeing the lookup =VLOOKUP(D:D,AZInputs,9) in the
cell contents, I see the pasted formula, =(C:C)*(F:F).

So far, so good. I don't have a problem up to here. But the pasted formula
is text instead of a true formula so even on the spread sheet I see
=(C:C)*(F:F) instead of the results of that formula (2x4=8). If I select
this cell, click the F2 button (edit), and then just hit enter, the cell
realizes it's a formula and gives me the results.

Finally my question: How can write a macro that would be the equivelant of
clicking F2 and hitting enter. When I click record macro, I get the results
below, which aren't generic enough.

Sub junk()
ActiveCell.FormulaR1C1 = "=(C:C)*(F:F)"
Range("F4").Select
End Sub

--

Dave Peterson


--

Dave Peterson

Macro to Edit Contents of Cell
 

Thanks! That was most helpful!

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()

Dim myRng As Range

'single area at a time
Set myRng = Selection.Areas(1)

With Intersect(myRng.EntireRow, ActiveSheet.Range("s1").EntireColumn)
.Copy
With .Offset(0, -13)
.PasteSpecial Paste:=xlPasteValues
With .Resize(, 2) 'same number of rows, but two columns
.Replace What:="=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With
End With
End With

End Sub

Still fixing columns F:G, right?

TKS_Mark wrote:

Dave Peterson,
That was a big help. Now, what if I select multiple rows? I changed the
formula as below, but instead of row, I want something like rows.
With ActiveSheet
With .Cells(ActiveCell.Row, "S")
.Copy
End With
With .Cells(ActiveCell.Row, "F")
.PasteSpecial Paste:=xlPasteValues
End With
With .Cells(ActiveCell.Row, "F").Resize(1, 2)
.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
End With

"Dave Peterson" wrote:

First, your formula is not in R1C1 Reference style--it's in A1 reference style.

Sub junk()
with activecell
.numberformat = "General" 'not text!
.Formula = .value
end with
End Sub

But I bet it would be quicker to
select the range
format it as General
and then do
Edit|Replace
what: = (equal sign)
with: =
replace all

You could record a macro if you need to.

TKS_Mark wrote:

I'm pulling in a formula to a cell based on the contents of another sheet.
Since Excel converts that formula to text, I'm writing a macro to copy from
one cell and paste values to another.

Example: One cell has =VLOOKUP(D:D,AZInputs,9). This give me a formula like
=(C:C)*(F:F) because that is the contents of column 9 on the named range
AZInputs.

My macro will copy this looked up formula from one cell to another and paste
values so that instead of seeing the lookup =VLOOKUP(D:D,AZInputs,9) in the
cell contents, I see the pasted formula, =(C:C)*(F:F).

So far, so good. I don't have a problem up to here. But the pasted formula
is text instead of a true formula so even on the spread sheet I see
=(C:C)*(F:F) instead of the results of that formula (2x4=8). If I select
this cell, click the F2 button (edit), and then just hit enter, the cell
realizes it's a formula and gives me the results.

Finally my question: How can write a macro that would be the equivelant of
clicking F2 and hitting enter. When I click record macro, I get the results
below, which aren't generic enough.

Sub junk()
ActiveCell.FormulaR1C1 = "=(C:C)*(F:F)"
Range("F4").Select
End Sub

--

Dave Peterson


--

Dave Peterson