| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| if statement? Want to populate whats in cell or N/A if cell is zer | Excel Worksheet Functions | |||
| IF THEN statement - Formula | Excel Discussion (Misc queries) | |||
| If Statement Checking Formula NOT Value in a Cell | Excel Discussion (Misc queries) | |||
| IF statement formula | Excel Worksheet Functions | |||
| Can I use IF statement with a formula? | Excel Worksheet Functions | |||
Linear Mode
