If statement in cell formula

  • Thread starter Thread starter Rick S.
  • Start date Start date
R

Rick S.

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
 
Does this do what you want?

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

Rick
 
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
 
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
 
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
 
Geez!
I was missing double quotes on
"" CONT""
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007
 
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
 
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
 
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. said:
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. said:
Geez!
I was missing double quotes on
"" CONT""
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007
Click to expand...
Click to expand...
 
Yes sir, that worked!
These "quotes" remind me of LISP!

Thanks again!
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007



Rick Rothstein (MVP - VB) said:
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. said:
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. said:
Geez!
I was missing double quotes on
"" CONT""
--
Regards

VBA.Newb.Confused
XP Pro
Office 2007



:

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



:

Does this do what you want?

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

Rick


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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top