You dropped one of the (doubled up) quote marks after the last comma. See if
this works for you...

ActiveCell.Formula = "=MID(D6,4,FIND(""-"",D6)-4)&IF(RIGHT(D6)="")"","
"" CONT"","""")"

Rick


"Rick S." wrote in message
...
Auuugghhhh!
LOL
Now I get an "Application defined error" 1004?
At the line below.
'======
ActiveCell.Formula = "=MID(D6,4,FIND(""-"",D6)-4)&IF(RIGHT(D6)="")"",""
CONT"","")"
'======

Code in its entirety.
'======
Sub SetOpNumIPI()
Application.DisplayAlerts = False
For i = 4 To Worksheets.Count 'Ignore first three sheets
Sheets(i).Activate 'start with first IPI data sheet
ActiveSheet.Unprotect "2000"
Range("D4").Select
Selection.NumberFormat = "General"
ActiveCell.Formula =
"=MID(D6,4,FIND(""-"",D6)-4)&IF(RIGHT(D6)="")"","" CONT"","")"
Range("B6").Select
ActiveCell.FormulaR1C1 = "SHT"
Range("D6").Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = _

"=MID(CELL(""filename"",R[-5]C[-3]),SEARCH(""]"",CELL(""filename"",R[-5]C[-3]))+1,1024)"
Range("D10").Select
ActiveSheet.Protect "2000"
Next i
Sheets("Master Sheet").Select
Application.DisplayAlerts = True
End Sub
'======
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007



"Rick S." wrote:

Geez!
I was missing double quotes on
"" CONT""
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007



"Rick S." wrote:

One more Q?
Why wont this accept as a formula? (it is one long string)
I know I have one or more "quotes" missing or out of place.
"======
ActiveCell.Formula = "=MID(D6,4,FIND(""-"",D6)-4)&IF(RIGHT(D6)="")"","
CONT","")"
'======
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007



"Rick Rothstein (MVP - VB)" wrote:

Does this do what you want?

=MID(D6,4,FIND("-",D6)-4)&IF(RIGHT(D6)=")"," CONT","")

Rick


"Rick S." wrote in message
...
I am better at VBA than worksheet formulas. :shrug:
I have this formula in a cell:
'======
=MID(D6,4,FIND("-",D6)-4)
'======
What I want to do is append "& " CONT"" to the formula as:
'======
=MID(D6,4,FIND("-",D6)-4) & " CONT"
'======
When the last character in cell "D6" is a ")".
I am hoping this can be a worksheet function. As of this posting I
do
this
manually on far too many worksheets.

As always, any help is appreciated!
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007