| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with "broken" formula! | Excel Discussion (Misc queries) | |||
| Mouse Scrolling broken when in formula bar | Excel Discussion (Misc queries) | |||
| i think its broken | Excel Discussion (Misc queries) | |||
| String manipulation from one cell broken up into multiple columns | Excel Programming | |||
| Get cell of broken Hyperlink | Excel Programming | |||
Linear Mode
