If statement in cell formula
 

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

If statement in cell formula
 

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

If statement in cell formula
 

Absolutely perfect!
I have been pulling my hair out trying to work the IF statement in there.
--
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

If statement in cell formula
 

You've got me confused. Why are you searching for a dash (minus)? You make a
reference to the last character of D6 being a ")" but there is no reference
to a ")" in your formula. Please give us examples of what is in D6 and what
you want the result to be.

Tyro

"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

If statement in cell formula
 

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

If statement in cell formula
 

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

If statement in cell formula
 

Mr. Rothstein took care of that part with this formula:
'======
=MID(D6,4,FIND("-",D6)-4)&IF(RIGHT(D6)=")"," CONT","")
'======

Example; Cell "D6" contains:
OP 90-1 (2) (which is the worksheet name)
'======
=MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1))+1,1024)
'======

The formula from Mr. Rothstein looks for the "-" sign then strips it and
displays the numeric value only.
Cell "D4" (where the formula resides) displays the numeric portion "90" and
if the end of the string for "D6" has a ")" then appends " CONT" for a final
string of:
"90 CONT" in cell "D4". If cell "D4" reads "90 CONT" then at a glance we
know it is a continuation of Operation 90.

This helps me in nearly one thousand workbooks with upto 20 worksheets each
from renaming cells "D4" and "D6" evertime there is a worksheet name change.
Worksheet name changes are inevitable in my line of work.
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007



"Tyro" wrote:

You've got me confused. Why are you searching for a dash (minus)? You make a
reference to the last character of D6 being a ")" but there is no reference
to a ")" in your formula. Please give us examples of what is in D6 and what
you want the result to be.

Tyro

"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

If statement in cell formula
 

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

If statement in cell formula
 

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

If statement in cell formula
 

Yes sir, that worked!
These "quotes" remind me of LISP!

Thanks again!
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007



"Rick Rothstein (MVP - VB)" wrote:

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