How construct a formula from cells with formulas as text?
 

I got a template sheet containing "formulas as text" (I have just wiped away
the "=" and formated the cells as text)

In another sheet I want to puzzel it together again as real formulas - I
need to put an "=" and the "formulas as text"

In the template sheet here called Sheet2:
Sheet20.Range("A1:A2") A1: ShtX!$C$29-ShtY!$C$29
A2: ShtX!$C$30-ShtY!$C$30

In Sheet1, where I want to have the formulas visual and working. I want the
result to be the products from the newborn formulas and the formulas visual
just as an ordinary formula in a cell use to.

Like if i would have written:
A1: = ShtX!$C$29-ShtY!$C$29
A2: = ShtX!$C$30-ShtY!$C$30

Easy but complex to describe, maybe? instead of writing the formula, I want
to create it from a "template" with the formula

I'm sure this is easy to do, but I've tried several ways know without
success.

/Regards

How construct a formula from cells with formulas as text?
 

Can you do something like:
newcell.formula = "=" & templatecell.value (or .formula)?

cheers
Simon

-----Original Message-----
I got a template sheet containing "formulas as text" (I
have just wiped away
the "=" and formated the cells as text)

In another sheet I want to puzzel it together again as
real formulas - I
need to put an "=" and the "formulas as text"

In the template sheet here called Sheet2:
Sheet20.Range("A1:A2") A1: ShtX!$C$29-ShtY!$C$29
A2: ShtX!$C$30-
ShtY!$C$30

In Sheet1, where I want to have the formulas visual and
working. I want the
result to be the products from the newborn formulas and
the formulas visual
just as an ordinary formula in a cell use to.

Like if i would have written:
A1: = ShtX!$C$29-ShtY!$C$29
A2: = ShtX!$C$30-ShtY!$C$30

Easy but complex to describe, maybe? instead of writing
the formula, I want
to create it from a "template" with the formula

I'm sure this is easy to do, but I've tried several ways
know without
success.

/Regards


.

Type "Mismatch": How construct a formula from cells with formulas as text?
 

Tank you, your suggestion works fine in test. When I make a application of
this, Iget error 13, "type mismatch". In the excel sheets, I actually have
copy - pasted the cells so they should be the same type. The only thing I
have done is deleted the "=" before the formulas.

Sub RestoreFormulas ()
Sheet118.Range("C29:G48").Cells.Formula = "=" &
Sheet203.Range("C29:G48").Cells.Value
End Sub

A typical template formula in one cell is: NkSc2!$D$29-NkSc1!$D$29
but that doesn't matter, I guess.

My question is open to anybody who can answer.

/Regards

"Simon Murphy" skrev i meddelandet
...
Can you do something like:
newcell.formula = "=" & templatecell.value (or .formula)?

cheers
Simon

-----Original Message-----
I got a template sheet containing "formulas as text" (I
have just wiped away
the "=" and formated the cells as text)

In another sheet I want to puzzel it together again as
real formulas - I
need to put an "=" and the "formulas as text"

In the template sheet here called Sheet2:
Sheet20.Range("A1:A2") A1: ShtX!$C$29-ShtY!$C$29
A2: ShtX!$C$30-
ShtY!$C$30

In Sheet1, where I want to have the formulas visual and
working. I want the
result to be the products from the newborn formulas and
the formulas visual
just as an ordinary formula in a cell use to.

Like if i would have written:
A1: = ShtX!$C$29-ShtY!$C$29
A2: = ShtX!$C$30-ShtY!$C$30

Easy but complex to describe, maybe? instead of writing
the formula, I want
to create it from a "template" with the formula

I'm sure this is easy to do, but I've tried several ways
know without
success.

/Regards


.

Type "Mismatch": How construct a formula from cells with formulas as text?
 

Sub RestoreFormulas ()
Dim cell as Range
for each cell in Sheet118.Range("C29:G48")
cell.Formula = "=" & Sheet203.Range(c.Address)
Next

End Sub

or

Sheet118.Range("C29:G48").Value = Sheet203.Range("C29:G48").Value
for each cell in sheet203.Range("C29:G48")
cell.Formula = "=" & cell.Value
Next

You might be able to use the Replace method to avoid the loop, but that
would presuppose a common unique starting string for each formula.

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy


wrote in message
...
Tank you, your suggestion works fine in test. When I make a application of
this, Iget error 13, "type mismatch". In the excel sheets, I actually have
copy - pasted the cells so they should be the same type. The only thing I
have done is deleted the "=" before the formulas.

Sub RestoreFormulas ()
Sheet118.Range("C29:G48").Cells.Formula = "=" &
Sheet203.Range("C29:G48").Cells.Value
End Sub

A typical template formula in one cell is: NkSc2!$D$29-NkSc1!$D$29
but that doesn't matter, I guess.

My question is open to anybody who can answer.

/Regards

"Simon Murphy" skrev i meddelandet
...
Can you do something like:
newcell.formula = "=" & templatecell.value (or .formula)?

cheers
Simon

-----Original Message-----
I got a template sheet containing "formulas as text" (I
have just wiped away
the "=" and formated the cells as text)

In another sheet I want to puzzel it together again as
real formulas - I
need to put an "=" and the "formulas as text"

In the template sheet here called Sheet2:
Sheet20.Range("A1:A2") A1: ShtX!$C$29-ShtY!$C$29
A2: ShtX!$C$30-
ShtY!$C$30

In Sheet1, where I want to have the formulas visual and
working. I want the
result to be the products from the newborn formulas and
the formulas visual
just as an ordinary formula in a cell use to.

Like if i would have written:
A1: = ShtX!$C$29-ShtY!$C$29
A2: = ShtX!$C$30-ShtY!$C$30

Easy but complex to describe, maybe? instead of writing
the formula, I want
to create it from a "template" with the formula

I'm sure this is easy to do, but I've tried several ways
know without
success.

/Regards


.

Type "Mismatch": How construct a formula from cells with formulas as text?
 

Thank you Tom,

The debugger want an object on line "for each cell in sheet..."

Can you help? I'll take a while unit I can analyze the new methods of yours
and I'm not sure I still will be able to find the answer.

Secondary: I use to have problem with the objects, is there any rule of
thumbs? I use to try to specify exactly the whole name of the object,
starting with "Application.Worksheet.Range(..." etc, and then you guys just
give me answers like "Me.Range.. and it works everywhere...:-)

I'm grateful for your help !

/Regards


"Tom Ogilvy" skrev i meddelandet
...
Sub RestoreFormulas ()
Dim cell as Range
for each cell in Sheet118.Range("C29:G48")
cell.Formula = "=" & Sheet203.Range(c.Address)
Next

End Sub

or

Sheet118.Range("C29:G48").Value = Sheet203.Range("C29:G48").Value
for each cell in sheet203.Range("C29:G48")
cell.Formula = "=" & cell.Value
Next

You might be able to use the Replace method to avoid the loop, but that
would presuppose a common unique starting string for each formula.

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy


wrote in message
...
Tank you, your suggestion works fine in test. When I make a application
of
this, Iget error 13, "type mismatch". In the excel sheets, I actually
have
copy - pasted the cells so they should be the same type. The only thing
I
have done is deleted the "=" before the formulas.

Sub RestoreFormulas ()
Sheet118.Range("C29:G48").Cells.Formula = "=" &
Sheet203.Range("C29:G48").Cells.Value
End Sub

A typical template formula in one cell is: NkSc2!$D$29-NkSc1!$D$29
but that doesn't matter, I guess.

My question is open to anybody who can answer.

/Regards

"Simon Murphy" skrev i meddelandet
...
Can you do something like:
newcell.formula = "=" & templatecell.value (or .formula)?

cheers
Simon

-----Original Message-----
I got a template sheet containing "formulas as text" (I
have just wiped away
the "=" and formated the cells as text)

In another sheet I want to puzzel it together again as
real formulas - I
need to put an "=" and the "formulas as text"

In the template sheet here called Sheet2:
Sheet20.Range("A1:A2") A1: ShtX!$C$29-ShtY!$C$29
A2: ShtX!$C$30-
ShtY!$C$30

In Sheet1, where I want to have the formulas visual and
working. I want the
result to be the products from the newborn formulas and
the formulas visual
just as an ordinary formula in a cell use to.

Like if i would have written:
A1: = ShtX!$C$29-ShtY!$C$29
A2: = ShtX!$C$30-ShtY!$C$30

Easy but complex to describe, maybe? instead of writing
the formula, I want
to create it from a "template" with the formula

I'm sure this is easy to do, but I've tried several ways
know without
success.

/Regards


.

Type "Mismatch": How construct a formula from cells with formulas as text?
 

I did have a typo in the first one:

Sub RestoreFormulas ()
Dim cell as Range
for each cell in Sheet118.Range("C29:G48")
cell.Formula = "=" & Sheet203.Range(cell.Address)
Next

End Sub

I don't see anything else that would cause a problem if you have sheets with
the above code names. If those are the tab names (the name you see on the
sheet tabe - rather than codenames which you would only see in the project
explorer in the VBE or if looking at the properties window of the sheet) you
should use:

Sub RestoreFormulas ()
Dim cell as Range
for each cell in worksheets("Sheet118").Range("C29:G48")
cell.Formula = "=" & worksheets("Sheet203").Range(cell.Address)
Next

End Sub

ME is only appropriate in a worksheet module. It is a way to reference the
sheet that owns the worksheet module.

--
Regards,
Tom Ogilvy




wrote in message
...
Thank you Tom,

The debugger want an object on line "for each cell in sheet..."

Can you help? I'll take a while unit I can analyze the new methods of
yours
and I'm not sure I still will be able to find the answer.

Secondary: I use to have problem with the objects, is there any rule of
thumbs? I use to try to specify exactly the whole name of the object,
starting with "Application.Worksheet.Range(..." etc, and then you guys
just
give me answers like "Me.Range.. and it works everywhere...:-)

I'm grateful for your help !

/Regards


"Tom Ogilvy" skrev i meddelandet
...
Sub RestoreFormulas ()
Dim cell as Range
for each cell in Sheet118.Range("C29:G48")
cell.Formula = "=" & Sheet203.Range(c.Address)
Next

End Sub

or

Sheet118.Range("C29:G48").Value = Sheet203.Range("C29:G48").Value
for each cell in sheet203.Range("C29:G48")
cell.Formula = "=" & cell.Value
Next

You might be able to use the Replace method to avoid the loop, but that
would presuppose a common unique starting string for each formula.

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy


wrote in message
...
Tank you, your suggestion works fine in test. When I make a
application
of
this, Iget error 13, "type mismatch". In the excel sheets, I actually
have
copy - pasted the cells so they should be the same type. The only
thing
I
have done is deleted the "=" before the formulas.

Sub RestoreFormulas ()
Sheet118.Range("C29:G48").Cells.Formula = "=" &
Sheet203.Range("C29:G48").Cells.Value
End Sub

A typical template formula in one cell is: NkSc2!$D$29-NkSc1!$D$29
but that doesn't matter, I guess.

My question is open to anybody who can answer.

/Regards

"Simon Murphy" skrev i
meddelandet
...
Can you do something like:
newcell.formula = "=" & templatecell.value (or .formula)?

cheers
Simon

-----Original Message-----
I got a template sheet containing "formulas as text" (I
have just wiped away
the "=" and formated the cells as text)

In another sheet I want to puzzel it together again as
real formulas - I
need to put an "=" and the "formulas as text"

In the template sheet here called Sheet2:
Sheet20.Range("A1:A2") A1: ShtX!$C$29-ShtY!$C$29
A2: ShtX!$C$30-
ShtY!$C$30

In Sheet1, where I want to have the formulas visual and
working. I want the
result to be the products from the newborn formulas and
the formulas visual
just as an ordinary formula in a cell use to.

Like if i would have written:
A1: = ShtX!$C$29-ShtY!$C$29
A2: = ShtX!$C$30-ShtY!$C$30

Easy but complex to describe, maybe? instead of writing
the formula, I want
to create it from a "template" with the formula

I'm sure this is easy to do, but I've tried several ways
know without
success.

/Regards


.

Type "Mismatch": How construct a formula from cells with formulas as text?
 

Tom, I tried the other sub instead and that one worked! Thank you!

Is there a way to identify - to know - when someone make a draganddrop on
sheet 118? To supervise the action or the moving of the cells in the
specific areas?

The loop take a while and really, I only need to run this sub if someone use
DragAndDrop on sheet 118. DragAndDrop ruin the sheet118 links/formulas and
therefore I need to have this template sheet approach.

Working code:
Sub RestoreFormulas()
Dim cell As Range
Blad118.Range("C29:G48").Value = Blad203.Range("C29:G48").Value
For Each cell In Blad118.Range("C29:G48")
cell.Formula = "=" & cell.Value
Next

Regards,
Gunnar Johansson

"Tom Ogilvy" skrev i meddelandet
...
Sub RestoreFormulas ()
Dim cell as Range
for each cell in Sheet118.Range("C29:G48")
cell.Formula = "=" & Sheet203.Range(c.Address)
Next

End Sub

or

Sheet118.Range("C29:G48").Value = Sheet203.Range("C29:G48").Value
for each cell in sheet203.Range("C29:G48")
cell.Formula = "=" & cell.Value
Next

You might be able to use the Replace method to avoid the loop, but that
would presuppose a common unique starting string for each formula.

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy


wrote in message
...
Tank you, your suggestion works fine in test. When I make a application
of
this, Iget error 13, "type mismatch". In the excel sheets, I actually
have
copy - pasted the cells so they should be the same type. The only thing
I
have done is deleted the "=" before the formulas.

Sub RestoreFormulas ()
Sheet118.Range("C29:G48").Cells.Formula = "=" &
Sheet203.Range("C29:G48").Cells.Value
End Sub

A typical template formula in one cell is: NkSc2!$D$29-NkSc1!$D$29
but that doesn't matter, I guess.

My question is open to anybody who can answer.

/Regards

"Simon Murphy" skrev i meddelandet
...
Can you do something like:
newcell.formula = "=" & templatecell.value (or .formula)?

cheers
Simon

-----Original Message-----
I got a template sheet containing "formulas as text" (I
have just wiped away
the "=" and formated the cells as text)

In another sheet I want to puzzel it together again as
real formulas - I
need to put an "=" and the "formulas as text"

In the template sheet here called Sheet2:
Sheet20.Range("A1:A2") A1: ShtX!$C$29-ShtY!$C$29
A2: ShtX!$C$30-
ShtY!$C$30

In Sheet1, where I want to have the formulas visual and
working. I want the
result to be the products from the newborn formulas and
the formulas visual
just as an ordinary formula in a cell use to.

Like if i would have written:
A1: = ShtX!$C$29-ShtY!$C$29
A2: = ShtX!$C$30-ShtY!$C$30

Easy but complex to describe, maybe? instead of writing
the formula, I want
to create it from a "template" with the formula

I'm sure this is easy to do, but I've tried several ways
know without
success.

/Regards


.

Type "Mismatch": How construct a formula from cells with formulas as text?
 

I am not sure how you would detect a drag and drop. Maybe just check after
the fact - looking for Error Values on the sheet.

On Error Resume Next
set rng = Blad118.Cells.SpecialCells(xlFormulas, xlErrors)
On Error goto 0
If not rng is nothing then
' at least some formulas are displaying an error value, high
' probability the formulas need to be restored.

--
Regards,
Tom Ogilvy

wrote in message
...
Tom, I tried the other sub instead and that one worked! Thank you!

Is there a way to identify - to know - when someone make a draganddrop on
sheet 118? To supervise the action or the moving of the cells in the
specific areas?

The loop take a while and really, I only need to run this sub if someone
use
DragAndDrop on sheet 118. DragAndDrop ruin the sheet118 links/formulas and
therefore I need to have this template sheet approach.

Working code:
Sub RestoreFormulas()
Dim cell As Range
Blad118.Range("C29:G48").Value = Blad203.Range("C29:G48").Value
For Each cell In Blad118.Range("C29:G48")
cell.Formula = "=" & cell.Value
Next

Regards,
Gunnar Johansson

"Tom Ogilvy" skrev i meddelandet
...
Sub RestoreFormulas ()
Dim cell as Range
for each cell in Sheet118.Range("C29:G48")
cell.Formula = "=" & Sheet203.Range(c.Address)
Next

End Sub

or

Sheet118.Range("C29:G48").Value = Sheet203.Range("C29:G48").Value
for each cell in sheet203.Range("C29:G48")
cell.Formula = "=" & cell.Value
Next

You might be able to use the Replace method to avoid the loop, but that
would presuppose a common unique starting string for each formula.

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy


wrote in message
...
Tank you, your suggestion works fine in test. When I make a
application
of
this, Iget error 13, "type mismatch". In the excel sheets, I actually
have
copy - pasted the cells so they should be the same type. The only
thing
I
have done is deleted the "=" before the formulas.

Sub RestoreFormulas ()
Sheet118.Range("C29:G48").Cells.Formula = "=" &
Sheet203.Range("C29:G48").Cells.Value
End Sub

A typical template formula in one cell is: NkSc2!$D$29-NkSc1!$D$29
but that doesn't matter, I guess.

My question is open to anybody who can answer.

/Regards

"Simon Murphy" skrev i
meddelandet
...
Can you do something like:
newcell.formula = "=" & templatecell.value (or .formula)?

cheers
Simon

-----Original Message-----
I got a template sheet containing "formulas as text" (I
have just wiped away
the "=" and formated the cells as text)

In another sheet I want to puzzel it together again as
real formulas - I
need to put an "=" and the "formulas as text"

In the template sheet here called Sheet2:
Sheet20.Range("A1:A2") A1: ShtX!$C$29-ShtY!$C$29
A2: ShtX!$C$30-
ShtY!$C$30

In Sheet1, where I want to have the formulas visual and
working. I want the
result to be the products from the newborn formulas and
the formulas visual
just as an ordinary formula in a cell use to.

Like if i would have written:
A1: = ShtX!$C$29-ShtY!$C$29
A2: = ShtX!$C$30-ShtY!$C$30

Easy but complex to describe, maybe? instead of writing
the formula, I want
to create it from a "template" with the formula

I'm sure this is easy to do, but I've tried several ways
know without
success.

/Regards


.

Type "Mismatch": How construct a formula from cells with formulas as text?
 

Hi Tom,

I just found out a much better way of restore the broken references (if
you DragAndDrop away some of the cells the link is pointing at). If I have a
hided cell (N29) on the sheet with correct reference, I can just copy and
paste it right on the range of cells that I want to restore. Or should be
able to, because I can't make the syntax..I get "Error 1004 Metod Range in
object Worksheet failed".

This I have on Sheet104, without success:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheet112.Range("N29").Copy
Sheet112.Range("C29:G48,C52:G71,C75:G94").PasteSpe cial (xlPasteAll)
End Sub

At first, I thougt it was that I had to have an Sheet112.Activate first, but
since I got the same error within an worksheet_Activate sub at Sheet112, it
seemed ridiculous...

Can you help me and I can skip the whole approach with restoring cells with
a template sheet etc....

/Regards


"Tom Ogilvy" skrev i meddelandet
...
I am not sure how you would detect a drag and drop. Maybe just check
after
the fact - looking for Error Values on the sheet.

On Error Resume Next
set rng = Blad118.Cells.SpecialCells(xlFormulas, xlErrors)
On Error goto 0
If not rng is nothing then
' at least some formulas are displaying an error value, high
' probability the formulas need to be restored.

--
Regards,
Tom Ogilvy

wrote in message
...
Tom, I tried the other sub instead and that one worked! Thank you!

Is there a way to identify - to know - when someone make a draganddrop
on
sheet 118? To supervise the action or the moving of the cells in the
specific areas?

The loop take a while and really, I only need to run this sub if someone
use
DragAndDrop on sheet 118. DragAndDrop ruin the sheet118 links/formulas
and
therefore I need to have this template sheet approach.

Working code:
Sub RestoreFormulas()
Dim cell As Range
Blad118.Range("C29:G48").Value = Blad203.Range("C29:G48").Value
For Each cell In Blad118.Range("C29:G48")
cell.Formula = "=" & cell.Value
Next

Regards,
Gunnar Johansson

"Tom Ogilvy" skrev i meddelandet
...
Sub RestoreFormulas ()
Dim cell as Range
for each cell in Sheet118.Range("C29:G48")
cell.Formula = "=" & Sheet203.Range(c.Address)
Next

End Sub

or

Sheet118.Range("C29:G48").Value = Sheet203.Range("C29:G48").Value
for each cell in sheet203.Range("C29:G48")
cell.Formula = "=" & cell.Value
Next

You might be able to use the Replace method to avoid the loop, but
that
would presuppose a common unique starting string for each formula.

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy


wrote in message
...
Tank you, your suggestion works fine in test. When I make a
application
of
this, Iget error 13, "type mismatch". In the excel sheets, I
actually
have
copy - pasted the cells so they should be the same type. The only
thing
I
have done is deleted the "=" before the formulas.

Sub RestoreFormulas ()
Sheet118.Range("C29:G48").Cells.Formula = "=" &
Sheet203.Range("C29:G48").Cells.Value
End Sub

A typical template formula in one cell is: NkSc2!$D$29-NkSc1!$D$29
but that doesn't matter, I guess.

My question is open to anybody who can answer.

/Regards

"Simon Murphy" skrev i
meddelandet
...
Can you do something like:
newcell.formula = "=" & templatecell.value (or .formula)?

cheers
Simon

-----Original Message-----
I got a template sheet containing "formulas as text" (I
have just wiped away
the "=" and formated the cells as text)

In another sheet I want to puzzel it together again as
real formulas - I
need to put an "=" and the "formulas as text"

In the template sheet here called Sheet2:
Sheet20.Range("A1:A2") A1: ShtX!$C$29-ShtY!$C$29
A2: ShtX!$C$30-
ShtY!$C$30

In Sheet1, where I want to have the formulas visual and
working. I want the
result to be the products from the newborn formulas and
the formulas visual
just as an ordinary formula in a cell use to.

Like if i would have written:
A1: = ShtX!$C$29-ShtY!$C$29
A2: = ShtX!$C$30-ShtY!$C$30

Easy but complex to describe, maybe? instead of writing
the formula, I want
to create it from a "template" with the formula

I'm sure this is easy to do, but I've tried several ways
know without
success.

/Regards


.