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


0
Minitman
5/26/2010 10:40:35 PM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
1245 Views

Similar Articles

[PageSpeed] 55

This will put the fromula in the cell.

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


"Minitman" <steve@minitmaidsofaustin.com> wrote in message 
news:do8rv55snr6oklopsdt1hr28nt6vetq5lt@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
>
> 


0
JLGWhiz
5/26/2010 11:03:34 PM
Range("B2").Formula = "=IF(A11=""Proximates"",""Click"","""")"


Gord Dibben  MS Excel MVP

On Wed, 26 May 2010 17:40:35 -0500, Minitman <steve@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
>

0
Gord
5/26/2010 11:23:31 PM
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@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
>>

0
Minitman
5/26/2010 11:58:06 PM
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@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@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
>>>

0
Gord
5/27/2010 12:15:00 AM
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@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@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


0
GS
5/27/2010 6:05:55 AM
Reply:

Similar Artilces:

How do I fix this?
I used the Startup tool to setup my application but I unchecked the Menu Bar and the Shortcut Menu Bar checkboxes. I need to make some changes but I can't get the Tool menu back. Help Please. Thanks Hold down the Shift key when you open your database. Rob "Ayo" <Ayo@discussions.microsoft.com> wrote in message news:C3A9F20A-231C-42CA-80C4-59B836CFE1AB@microsoft.com... >I used the Startup tool to setup my application but I unchecked the Menu >Bar > and the Shortcut Menu Bar checkboxes. I need to make some changes but I > can't > get the Tool menu ba...

Automatic resizing of cell
I use Excel for my job. When I enter a long text sentence into a cell that is larger than the cell, it gets covered up because the text wrapping is on. I want the sentence to wrap around to the next line, but Excel doesn't automatically adjust the size of the cell when it sees that the text is longer. I end up printing out my work and only half of the sentence is there. I can resize it myself, but I want Excel to resize it when it sees that it's covering it. I can't see why anyone would actually WANT Excel to cover it up. Which version of Excel are you using. I'm u...

Need to convert 2002 back t0 while laptop is fixed
My laptop with Money 2002 died and had to be sent in for service which will probably be about a month. The good news is that I backup up all my data files the day before and burned them to CD. My office machine is running Windows 98 SE and Money 2001 but it will not read the 2002 files. I can't install XP because the machine will not support it. I used a friend's machine and can open my backup file but I don't want to leave my financial data there. Is there some way I can transfer the data to Excel or save it in a backwardly compatible format? I tried to synchronis...

Advanced formulas.
very soon i will be starting a tafe course involving advanced spreadsheets, my question is: Is there a publication available ,where i can access all posible formulas for this work ? Hi Cobber_Oz! Functions lists available from my email below. Also see: Peter Noneley: http://homepage.ntlworld.com/noneley/ -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "COBBER_OZ" <anonymous@discussions.microsoft.com> wrote in message news:C6E477CF-CE34-40B...

Page Numbers in Cells
This is what I am doing.... I have a monthly document (identical in appearance but not content) which can expand or shrink dependant on the number of items which are added to it. The top section is printed on each page and one of the cells needs to state the page number… ie Page 1 of 2 etc. Is there a formula that can be put in the cell that registers the printed page number? Dave Why not put the page number in the header or footer. Got to Page Setup and the Header/Footer Tab. Select the pull down menu for header or footer and page # is an option. "DC appleyards" <...

How can I insert a carriage return and line feed into a cell ?
I am trying to display a statistic with its t_stat in the same cell, one over the other. Try pressing Alt+Enter or Format the cell to wrap from Format|Cells tj "gpm" wrote: > I am trying to display a statistic with its t_stat in the same cell, one over > the other. In a concatenation you can use ="line one" & char(10) & "line two" & char(10) & "line three" You will have to turn on cell wrap for such cells Format, cells, aligment, cell wrap Failure to turn on cell wrap will show the square printer's bullets asso...

sum column based on single cell match?
This seems so simple and yet the solution eludes me... In row 2 I want Excel to match a cell's contents (ex: find 3200). Once finding the column that matches, I want it to sum rows 3:94. Thank you in advance for your assistance. Something like this: =SUM(INDEX(A3:G94,,MATCH(3200,A2:G2,0)) In this example, you have columns A-G for your data. -- Best Regards, Luke M "adodson" <adodson@discussions.microsoft.com> wrote in message news:F7C768FE-2ED0-4FB4-881E-CDC9C7CE75DB@microsoft.com... > This seems so simple and yet the solution eludes me... ...

Fixing column width
I have a spreadsheet in which I have set the widths of certain columns using Autofit. The problem is that I often have to add to the spreadsheet by importing data from a text file. Every time I do that, the column width changes to the width of the new data (which is usually narrower than what I wanted). Is there a way to prevent that from happening? Thanks, Cliff Lewis Assuming you're using the Data, Import Data command, when you get to the final "Import Data" dialog there is a "Properties" button (in Excel 2003 at least). One of those properties is Adjust column w...

Help with Formulas #2
I need help with creating a formula. I want to count the total number of individuals who meet a certain criteria in one column and a certain criteria in another column. For example: I want to find out the total number of people who live in Germany and went on a specific trip. For argument sake let's say column A is at lists only Y or N for yes and no, and column B is a list of Countries such as Germany, America, etc. I would like to figure out the total number of individuals who have a Y in column A and the word Germany in column B. This is what I have done so far and it works for co...

The great disappearing hot fix
Has anyone experienced anything like this? Yesterday, one of our users complained to me about some quirk in GP eConnect processing that I had thought we fixed in December of 2007 via a Hot Fix to eConnect. Well, when I checked our server it was as if the hot fix was never installed (the eConnect release version was the basic 9.0.3.0, instead of 9.0.3.4). The Hot Fix was also missing from Add/Remove Programs. I went to reinstall the hot fix, and when the install program started it gave me the option to either Repair or Remove the hot fix. So, something was still present tha...

Passing a value in VBA using an offset
I have code that executes when I double click on a cell. The code inserts the data validation source into a combobox to ease the input choice. It refers to the cell I double clicked as "target" I want to add a new feature. Before anything else happens, I want to filter the data validation source (many of the entries are for dates that don't apply. the cell above "target" has a date. I want to copy the month of that date into a fixed cell ("A1") and then use that date in an advanced filter on the data validation source. Hope you're with me so far. T...

Array formulae do not recalculate
I have a large number of array formulae in my sheet. I update the supporting data and the formulae do not recalculate. The only way i can get a recalc is to F2 and then ctrl+shift+enter. How can I get all of the formulae to recalc. Manual recalc does not work. Does Automatic Calculation mode work? If excel is confused about whether it should recalculate a formula, this sometimes wakes excel up and stops the confusion. Select all the cells on that sheet (ctrl-a a few times should do it) Edit|replace what: = (equal sign) with: = replace all Excel will see that ...

How can I merge unlocked cells in a worksheet that has been protec
In my worksheet, I turned on "Protect Sheet" so that users can't view or modify locked cells. But since I still wanted them to be able to modify unlocked cells any way they wanted, I selected all the options in the list of "Allow all users of this worksheet to" features in the "Protect Sheet" dialogue box (except for "select locked cells" obviously). However, the users can't seem to merge unlocked cells. Is there a way of including this functionality in the allowed actions? You could create a macro to unprotect the sheet, merge the sele...

Formula with no result
I have clients that I am intruducing starting with a date. After exactly 10 days, I have to pay them some money. I would like to be notified by changing colour of the their rows, and not changing back until I complete the cell of paiment, that is, of course, in the same row. After I reveived help from this forum, I've tried to do the next things: I have: A1 Z1 - TO PAY 10/07/2008 0 15/07/2008 30 05/07/2008 50 Z1 - TO PAY cell ads the total amount the money that I have to pay in other cells. This is wh...

Error importing dates from fixed width txt files
I'm trying to revise an existing Import Spec to include new fields added to a fixed width text file. If I import without making any revisions the dates import correctly. They are on the file as CCYYMMDD and the data field in the Spec is Date/Time and I've deselected 4 digit year. My new date fields were added to the text file as YYMMDD and I select Date/Time data field for the new fields. However, with the revisions made adding the new fields none of the date fields import now with the revised spec. Another user created the spec and we're both using Access 2003 with th...

Copy Only Visible Cell
Hi Everyone, Is it posible to copy a block of cels with combined hidden collumn and row to another sheet or cells without those hidden field to be copied ? When i try to copy this kind of cells to another sheets/cells, excel copy all cells including the hidden cells. I just want to copy cels that is visible not including those hidden cells, Is there a way around this ? Thanks in Advance Hi Robert, Select the desired range. Click Edit - Go To. Click Special then select Visible Cells Only then OK. Copy the selected range. David In article <1125983768.158532.315240@g44g2000cwa.goog...

Cell Box - one of the 4 cell lines is open
What was the set of search "words" to find the answer... please? I have spent an hour. ( I don't want to tell you that I spent more time - mentioned). Oh yea! The lines are call borders and are part of cell formatting. Format > Cells... > Border > Outline > OK -- Gary''s Student - gsnu200826 "MUTTMIND" wrote: > What was the set of search "words" to find the answer... please? I have > spent an hour. ( I don't want to tell you that I spent more time - > mentioned). Oh yea! No worky. Excel 2007 - home tab - cells (ins...

Fix
What does the word FIX mean on Excel 2000? It is in the lower right hand corner 3 boxes away from NUM, 4 boxes from CAPS. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ It means you have turned on fixed decimals places under tools>options>edit, so if you have 2 fixed decimal places and enter 20 in a cell it will change into 0.2 Just uncheck it if you don't want it -- Regards, Peo Sjoblom "zacharias" <zacharias.vdc4c@excelforum-nospam.com&...

Insert rows with Formulas
Hi all, I have a spreadsheet that starting at row 18 contains the following information A18=M$2 B18= Datavalidation indirect ($M$2) C18=IF(B18="No More Options",VLOOKUP($B18,INDIRECT(CONCATENATE($M$2,"_info")),1,FALSE),IF(B19<2,VLOOKUP($B18,INDIRECT(CONCATENATE($M$2,"_info")),1,FALSE),CONCATENATE(B19," x ",VLOOKUP($B18,INDIRECT(CONCATENATE($M$2,"_info")),1,FALSE))) E18=IF(ISERROR(VLOOKUP($B18,INDIRECT(CONCATENATE($M$2,"_info")),2,FALSE)),"",VLOOKUP($B18,INDIRECT(CONCATENATE($M$2,"_info")),2,FALSE)*B19) G18=C...

Confusing output from cells
:confused: I have made a spreadsheet that is gathering details from the user an then forming a string. However, in the 6th section of 8 it is causing problems. the formulas are all much the same. an example is shown below:- =if(b268="","",f88) Now, B268 is empty, so the "" criteria is met, but instead of bein nothing, the spreadsheet is counting this as a space, so that when th text is pasted into another program, extra spaces are added! However the Len of the above cell is 0, meaning it has no value whatsoever. Does anyone know anything about this? Than...

Fixed Assets Quick Links
Does anyone know why I cannot customize the homepage with links to Fixed Asset windows? ...

Macro to clean empty cells
As I have had some great help from this group before, here is another request. Am looking to clean up some sheets in various workbooks in Excel 2007 Anybody able to help me out with a macro that will look for empty cells and then clear them out of all formats or hidden characters etc. that they may have but cant be seen. Will only need to run it on individual named sheets rather than on whole workbook many thanks ...

How to protect cells?
I would like to protect a range of cells (A2:D24) with a password for a group of users that would make changes to cells in this range. Then on the same sheet, also protect a range of cells (E2:E24) from everyone but me. Is this possible? Thanks. Mark, Please don't multipost. See the thread in .programming. John "Mark F." <m7829@yahoo.com> wrote in message news:FSzQb.10797$6o4.894@fe2.texas.rr.com... > I would like to protect a range of cells (A2:D24) with a password for a > group of users that would make changes to cells in this range. Then on > the same she...

Help Needed: "IF" formula/logic too limited
I need to determine a value that is predicated upon the selections of about 6 or so drop down menus. It'd be General Text within drop-downs but final value is an Accountant price Basically if someone selects Option #1 out of Cells A-G's drop down menus, then the value of H1 = a cell on another page that fits the category of 1- 1-1-1-1-1 from the drop down menus. I don't know how to do this. I imagine it's possible but have no clue. Your help is greatly appreciated. From what you say, the meat of this problem lies in the layout/format of the data you have in this "...

Negative NBV in Fixed asset module
Does anyone konw why negative NBV will be happened in the fixed asset module and how to avoid this happen ? example like the asset cost 10,000 but it total deprecation amount (fully deprecaited) is 15,000. thanks ...