 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008If 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!  SignatureRegards 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: [quoted text clipped - 11 lines] > > As always, any help is appreciated! Absolutely perfect! I have been pulling my hair out trying to work the IF statement in there. SignatureRegards VBA.Newb.Confused XP Pro Office 2007 > Does this do what you want? > [quoted text clipped - 17 lines] > > > > As always, any help is appreciated! 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","")" '====== SignatureRegards VBA.Newb.Confused XP Pro Office 2007 > Does this do what you want? > [quoted text clipped - 17 lines] > > > > As always, any help is appreciated! Geez! I was missing double quotes on "" CONT"" SignatureRegards VBA.Newb.Confused XP Pro Office 2007 > One more Q? > Why wont this accept as a formula? (it is one long string) [quoted text clipped - 25 lines] > > > > > > As always, any help is appreciated! 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 '====== SignatureRegards VBA.Newb.Confused XP Pro Office 2007 > Geez! > I was missing double quotes on [quoted text clipped - 29 lines] > > > > > > > > As always, any help is appreciated! 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 > Auuugghhhh! > LOL [quoted text clipped - 65 lines] >> > > > >> > > > As always, any help is appreciated! Yes sir, that worked! These "quotes" remind me of LISP! Thanks again! SignatureRegards 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... [quoted text clipped - 73 lines] > >> > > > > >> > > > As always, any help is appreciated! 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 >I am better at VBA than worksheet formulas. :shrug: > I have this formula in a cell: [quoted text clipped - 11 lines] > > As always, any help is appreciated! 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. SignatureRegards 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 [quoted text clipped - 18 lines] > > > > As always, any help is appreciated! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.