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: Minitman on 26 May 2010 18:40 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
From: JLGWhiz on 26 May 2010 19:03 This will put the fromula in the cell. Range("B2").Formula = "=IF(All=""Proximities"",""Click"","""")" "Minitman" <steve(a)minitmaidsofaustin.com> wrote in message news:do8rv55snr6oklopsdt1hr28nt6vetq5lt(a)4ax.com... > 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 > >
From: Gord Dibben on 26 May 2010 19:23 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 >
From: Minitman on 26 May 2010 19:58 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 >>
From: Gord Dibben on 26 May 2010 20:15
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 <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 >>> |