Fix Broken Cell Formula with VBA
 

Greetings,

I have a cell formula that is damaged each time I run a delete to
remove excess rows before a paste & copy. this formula links to a
cell that is always deleted. So I am trying to repair this formula at
the end of each cycle.

Here is the correct formula:
=IF(A11="Proximates","Click","")

Here is the same formula after the delete:
=IF(#REF!="Proximates","Click","")

I tried to fix this in the code that does the finale copy & paste with
this:
Range("B2").Formula = "=IF(A11=" & Chr(34) & "Proximates" & _
Chr(34) & "," & Chr(34) & "Click" & Chr(34) & "," & Chr(34) _
& " & Chr(34) & ")"

(B2 is where this formula is sitting)

Debug did not like the syntax!!!

Anyone have any ideas, thoughts or suggestions?

Any help is appreciated.

-Minitman

Fix Broken Cell Formula with VBA
 

This will put the fromula in the cell.

Range("B2").Formula = "=IF(All=""Proximities"",""Click"","""")"


"Minitman" wrote in message
...
Greetings,

I have a cell formula that is damaged each time I run a delete to
remove excess rows before a paste & copy. this formula links to a
cell that is always deleted. So I am trying to repair this formula at
the end of each cycle.

Here is the correct formula:
=IF(A11="Proximates","Click","")

Here is the same formula after the delete:
=IF(#REF!="Proximates","Click","")

I tried to fix this in the code that does the finale copy & paste with
this:
Range("B2").Formula = "=IF(A11=" & Chr(34) & "Proximates" & _
Chr(34) & "," & Chr(34) & "Click" & Chr(34) & "," & Chr(34) _
& " & Chr(34) & ")"

(B2 is where this formula is sitting)

Debug did not like the syntax!!!

Anyone have any ideas, thoughts or suggestions?

Any help is appreciated.

-Minitman

Fix Broken Cell Formula with VBA
 

Range("B2").Formula = "=IF(A11=""Proximates"",""Click"","""")"


Gord Dibben MS Excel MVP

On Wed, 26 May 2010 17:40:35 -0500, Minitman
wrote:

Greetings,

I have a cell formula that is damaged each time I run a delete to
remove excess rows before a paste & copy. this formula links to a
cell that is always deleted. So I am trying to repair this formula at
the end of each cycle.

Here is the correct formula:
=IF(A11="Proximates","Click","")

Here is the same formula after the delete:
=IF(#REF!="Proximates","Click","")

I tried to fix this in the code that does the finale copy & paste with
this:
Range("B2").Formula = "=IF(A11=" & Chr(34) & "Proximates" & _
Chr(34) & "," & Chr(34) & "Click" & Chr(34) & "," & Chr(34) _
& " & Chr(34) & ")"

(B2 is where this formula is sitting)

Debug did not like the syntax!!!

Anyone have any ideas, thoughts or suggestions?

Any help is appreciated.

-Minitman

Fix Broken Cell Formula with VBA
 

Thanks Gord & GL.

You both come up with a correct (and identical) solution that works.

Again, thank you both very much.

-Minitman


On Wed, 26 May 2010 16:23:31 -0700, Gord Dibben <gorddibbATshawDOTca
wrote:

Range("B2").Formula = "=IF(A11=""Proximates"",""Click"","""")"


Gord Dibben MS Excel MVP

On Wed, 26 May 2010 17:40:35 -0500, Minitman
wrote:

Greetings,

I have a cell formula that is damaged each time I run a delete to
remove excess rows before a paste & copy. this formula links to a
cell that is always deleted. So I am trying to repair this formula at
the end of each cycle.

Here is the correct formula:
=IF(A11="Proximates","Click","")

Here is the same formula after the delete:
=IF(#REF!="Proximates","Click","")

I tried to fix this in the code that does the finale copy & paste with
this:
Range("B2").Formula = "=IF(A11=" & Chr(34) & "Proximates" & _
Chr(34) & "," & Chr(34) & "Click" & Chr(34) & "," & Chr(34) _
& " & Chr(34) & ")"

(B2 is where this formula is sitting)

Debug did not like the syntax!!!

Anyone have any ideas, thoughts or suggestions?

Any help is appreciated.

-Minitman

Fix Broken Cell Formula with VBA
 

Glad to help.

I would look at a re-design of your sheet so's you don't have to rebuild the
formula.


Gord

On Wed, 26 May 2010 18:58:06 -0500, Minitman
wrote:

Thanks Gord & GL.

You both come up with a correct (and identical) solution that works.

Again, thank you both very much.

-Minitman


On Wed, 26 May 2010 16:23:31 -0700, Gord Dibben <gorddibbATshawDOTca
wrote:

Range("B2").Formula = "=IF(A11=""Proximates"",""Click"","""")"


Gord Dibben MS Excel MVP

On Wed, 26 May 2010 17:40:35 -0500, Minitman
wrote:

Greetings,

I have a cell formula that is damaged each time I run a delete to
remove excess rows before a paste & copy. this formula links to a
cell that is always deleted. So I am trying to repair this formula at
the end of each cycle.

Here is the correct formula:
=IF(A11="Proximates","Click","")

Here is the same formula after the delete:
=IF(#REF!="Proximates","Click","")

I tried to fix this in the code that does the finale copy & paste with
this:
Range("B2").Formula = "=IF(A11=" & Chr(34) & "Proximates" & _
Chr(34) & "," & Chr(34) & "Click" & Chr(34) & "," & Chr(34) _
& " & Chr(34) & ")"

(B2 is where this formula is sitting)

Debug did not like the syntax!!!

Anyone have any ideas, thoughts or suggestions?

Any help is appreciated.

-Minitman

Fix Broken Cell Formula with VBA
 

Gord Dibben wrote on 5/26/2010 :
Glad to help.

I would look at a re-design of your sheet so's you don't have to rebuild the
formula.


Gord

I was going to suggest the same thing, since the formula does use a
relative ref. It suggests to me that a defined name should be used to
prevent this from happening.

--
Garry


On Wed, 26 May 2010 18:58:06 -0500, Minitman
wrote:

Thanks Gord & GL.

You both come up with a correct (and identical) solution that works.

Again, thank you both very much.

-Minitman


On Wed, 26 May 2010 16:23:31 -0700, Gord Dibben <gorddibbATshawDOTca
wrote:

Range("B2").Formula = "=IF(A11=""Proximates"",""Click"","""")"


Gord Dibben MS Excel MVP

On Wed, 26 May 2010 17:40:35 -0500, Minitman
wrote:

Greetings,

I have a cell formula that is damaged each time I run a delete to
remove excess rows before a paste & copy. this formula links to a
cell that is always deleted. So I am trying to repair this formula at
the end of each cycle.

Here is the correct formula:
=IF(A11="Proximates","Click","")

Here is the same formula after the delete:
=IF(#REF!="Proximates","Click","")

I tried to fix this in the code that does the finale copy & paste with
this:
Range("B2").Formula = "=IF(A11=" & Chr(34) & "Proximates" & _
Chr(34) & "," & Chr(34) & "Click" & Chr(34) & "," & Chr(34) _
& " & Chr(34) & ")"

(B2 is where this formula is sitting)

Debug did not like the syntax!!!

Anyone have any ideas, thoughts or suggestions?

Any help is appreciated.

-Minitman

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc