Fix Broken Cell Formula with VBA
in [Programming]
|
Prev: To show a message box if the mouse is over a particular cell??
Next: VBA to insert .xlborder if cell value not equal to previous cell
From: GS on 27 May 2010 02:05 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 <steve(a)minitmaidsofaustin.com> > 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 <steve(a)minitmaidsofaustin.com> >>> 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 |