| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Typing vs VBA
I have a 9-column spreadsheet where Col H is an IF that points to a pivot
table on a separate worksheet (same .xls file, though). Column I is an IF that first points to the cell in Col H and then pulls additional data from the same pivot table. I can key the function in Col I and it works without a problem. I am trying to get a macro recorded to enter the function and I get an Unable to Record dialog when I key in the function with the macro recorder turned on. Ultimately, the function I want to get into I2 is this: =IF(INDIRECT("RC8", FALSE)="Auto No Run", IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3, "TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Error", "ManAuto", "Auto"), "Auto Error", IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3, "TC ID", INDIRECT("RC1", FALSE), "Comp Status", "UnDev", "ManAuto", "Auto")0, "Auto UnDev", IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3, "TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Maint", "ManAuto", "Auto")0, "Auto Maint", "Eval This Row"))), IF(INDIRECT("RC8", FALSE)="Run Auto", "", "")) So after repeated attempts to get this into the cell... I've keyed it into the editor with double double quotes for the editor. 1. =IF(INDIRECT("RC8", FALSE)="Auto No Run", "XXXX", "ZZZZ") 2. XXXX: IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3,"TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Error", "ManAuto", "Auto"), "Auto Error", "AAAA") 3. AAAA: IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3,"TC ID", INDIRECT("RC1", FALSE), "Comp Status", "UnDev", "ManAuto", "Auto"), "Auto Script", "BBBB") 4. BBBB: IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3,"TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Maint", "ManAuto", "Auto"), "Auto Maint", "???") 5. ZZZZ: IF(INDIRECT("RC8", FALSE)="Run Auto", "", "")) I got the first 2 pieces in without throwing the error. But once I replace "AAAA" with the piece of function... Unable to Record. Before I MacGuyver a different solution that I have in mind.... can anyone shed some light on why this is happening? Thanks. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Typing vs VBA
I think the problem dues to the fact that Excel cells only allow certain
numbers of IF in a formula, like 7. If you have more than 7 nested IF, it is better use VBA to compute. Jay "JG" wrote: I have a 9-column spreadsheet where Col H is an IF that points to a pivot table on a separate worksheet (same .xls file, though). Column I is an IF that first points to the cell in Col H and then pulls additional data from the same pivot table. I can key the function in Col I and it works without a problem. I am trying to get a macro recorded to enter the function and I get an Unable to Record dialog when I key in the function with the macro recorder turned on. Ultimately, the function I want to get into I2 is this: =IF(INDIRECT("RC8", FALSE)="Auto No Run", IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3, "TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Error", "ManAuto", "Auto"), "Auto Error", IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3, "TC ID", INDIRECT("RC1", FALSE), "Comp Status", "UnDev", "ManAuto", "Auto")0, "Auto UnDev", IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3, "TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Maint", "ManAuto", "Auto")0, "Auto Maint", "Eval This Row"))), IF(INDIRECT("RC8", FALSE)="Run Auto", "", "")) So after repeated attempts to get this into the cell... I've keyed it into the editor with double double quotes for the editor. 1. =IF(INDIRECT("RC8", FALSE)="Auto No Run", "XXXX", "ZZZZ") 2. XXXX: IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3,"TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Error", "ManAuto", "Auto"), "Auto Error", "AAAA") 3. AAAA: IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3,"TC ID", INDIRECT("RC1", FALSE), "Comp Status", "UnDev", "ManAuto", "Auto"), "Auto Script", "BBBB") 4. BBBB: IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3,"TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Maint", "ManAuto", "Auto"), "Auto Maint", "???") 5. ZZZZ: IF(INDIRECT("RC8", FALSE)="Run Auto", "", "")) I got the first 2 pieces in without throwing the error. But once I replace "AAAA" with the piece of function... Unable to Record. Before I MacGuyver a different solution that I have in mind.... can anyone shed some light on why this is happening? Thanks. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Typing vs VBA
There are only 5 in that function, and I thought it was 7 nested IFs was the
limitation. Thanks though. :) "Jay" wrote: I think the problem dues to the fact that Excel cells only allow certain numbers of IF in a formula, like 7. If you have more than 7 nested IF, it is better use VBA to compute. Jay "JG" wrote: I have a 9-column spreadsheet where Col H is an IF that points to a pivot table on a separate worksheet (same .xls file, though). Column I is an IF that first points to the cell in Col H and then pulls additional data from the same pivot table. I can key the function in Col I and it works without a problem. I am trying to get a macro recorded to enter the function and I get an Unable to Record dialog when I key in the function with the macro recorder turned on. Ultimately, the function I want to get into I2 is this: =IF(INDIRECT("RC8", FALSE)="Auto No Run", IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3, "TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Error", "ManAuto", "Auto"), "Auto Error", IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3, "TC ID", INDIRECT("RC1", FALSE), "Comp Status", "UnDev", "ManAuto", "Auto")0, "Auto UnDev", IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3, "TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Maint", "ManAuto", "Auto")0, "Auto Maint", "Eval This Row"))), IF(INDIRECT("RC8", FALSE)="Run Auto", "", "")) So after repeated attempts to get this into the cell... I've keyed it into the editor with double double quotes for the editor. 1. =IF(INDIRECT("RC8", FALSE)="Auto No Run", "XXXX", "ZZZZ") 2. XXXX: IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3,"TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Error", "ManAuto", "Auto"), "Auto Error", "AAAA") 3. AAAA: IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3,"TC ID", INDIRECT("RC1", FALSE), "Comp Status", "UnDev", "ManAuto", "Auto"), "Auto Script", "BBBB") 4. BBBB: IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3,"TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Maint", "ManAuto", "Auto"), "Auto Maint", "???") 5. ZZZZ: IF(INDIRECT("RC8", FALSE)="Run Auto", "", "")) I got the first 2 pieces in without throwing the error. But once I replace "AAAA" with the piece of function... Unable to Record. Before I MacGuyver a different solution that I have in mind.... can anyone shed some light on why this is happening? Thanks. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Typing vs VBA
You need to replace all the quotes with double quotes
Copy your formula into another cell preceded with an apostrophe do search/replace " (single-quote) with (say) a # Copy the new formula into the VBE but discard the leading apostrophe sFmla = "=my new formula with # replacing each single quote" sFmla = Replace(sFmla, "#", """") Regards, Peter T "JG" wrote in message ... I have a 9-column spreadsheet where Col H is an IF that points to a pivot table on a separate worksheet (same .xls file, though). Column I is an IF that first points to the cell in Col H and then pulls additional data from the same pivot table. I can key the function in Col I and it works without a problem. I am trying to get a macro recorded to enter the function and I get an Unable to Record dialog when I key in the function with the macro recorder turned on. Ultimately, the function I want to get into I2 is this: =IF(INDIRECT("RC8", FALSE)="Auto No Run", IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3, "TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Error", "ManAuto", "Auto"), "Auto Error", IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3, "TC ID", INDIRECT("RC1", FALSE), "Comp Status", "UnDev", "ManAuto", "Auto")0, "Auto UnDev", IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3, "TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Maint", "ManAuto", "Auto")0, "Auto Maint", "Eval This Row"))), IF(INDIRECT("RC8", FALSE)="Run Auto", "", "")) So after repeated attempts to get this into the cell... I've keyed it into the editor with double double quotes for the editor. 1. =IF(INDIRECT("RC8", FALSE)="Auto No Run", "XXXX", "ZZZZ") 2. XXXX: IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3,"TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Error", "ManAuto", "Auto"), "Auto Error", "AAAA") 3. AAAA: IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3,"TC ID", INDIRECT("RC1", FALSE), "Comp Status", "UnDev", "ManAuto", "Auto"), "Auto Script", "BBBB") 4. BBBB: IF(GETPIVOTDATA("Comp Status", TCCompositionAnalysis!$A$3,"TC ID", INDIRECT("RC1", FALSE), "Comp Status", "Maint", "ManAuto", "Auto"), "Auto Maint", "???") 5. ZZZZ: IF(INDIRECT("RC8", FALSE)="Run Auto", "", "")) I got the first 2 pieces in without throwing the error. But once I replace "AAAA" with the piece of function... Unable to Record. Before I MacGuyver a different solution that I have in mind.... can anyone shed some light on why this is happening? Thanks. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Typing vs VBA
I'm not following you here. I don't have any single quotes in my formula.
If you're referring to double quotes, I already have 2 double quotes where a double quote would appear in the cell. The root of the issue has something to do with the fact that I can key this thing in and it works just fine, but not if it's recorded/edited. "Peter T" wrote: You need to replace all the quotes with double quotes Copy your formula into another cell preceded with an apostrophe do search/replace " (single-quote) with (say) a # Copy the new formula into the VBE but discard the leading apostrophe sFmla = "=my new formula with # replacing each single quote" sFmla = Replace(sFmla, "#", """") Regards, Peter T |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Typing vs VBA
Maybe I misunderstood your problem. Try this manually in say A1
="ABC" & 123 To write the same formula in VBA you need the double quotes I mentioned, like this Range("A3").Formula = "=""ABC"" & 123" With a simple formula it's easy enough to include those extra quotes. However with your big formula probably better with a bit of help along the lines I suggested. If this is not the issue get back and I'll have another look. But show us your actual code, ie what you have in the VBE. Head the module Option Explicit and declare your variables. State which line an error occurs, or the incorrect resultant formula vs expected formula. Regards, Peter T "JG" wrote in message ... I'm not following you here. I don't have any single quotes in my formula. If you're referring to double quotes, I already have 2 double quotes where a double quote would appear in the cell. The root of the issue has something to do with the fact that I can key this thing in and it works just fine, but not if it's recorded/edited. "Peter T" wrote: You need to replace all the quotes with double quotes Copy your formula into another cell preceded with an apostrophe do search/replace " (single-quote) with (say) a # Copy the new formula into the VBE but discard the leading apostrophe sFmla = "=my new formula with # replacing each single quote" sFmla = Replace(sFmla, "#", """") Regards, Peter T |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Typing vs VBA
The following gives me a Run-time error '1004':
Sub Macro6() ' ' Range("I2").Select ActiveCell.FormulaR1C1 = _ "=IF(INDIRECT(""RC8"", FALSE)=""Auto No Run"", IF(GETPIVOTDATA(""Comp Status"", TCCompositionAnalysis!$A$3, ""TC ID"", INDIRECT(""RC1"", FALSE), ""Comp Status"", ""Error"", ""ManAuto"", ""Auto""), ""Auto Error"", IF(GETPIVOTDATA(""Comp Status"", TCCompositionAnalysis!$A$3, ""TC ID"", INDIRECT(""RC1"", FALSE), ""Comp Status"", ""UnDev"", ""ManAuto"", ""Auto"")0, ""Auto UnDev"", IF(GETPIVOTDATA(""Comp Status"", TCCompositionAnalysis!$A$3, ""TC ID"", INDIRECT(""RC1"", FALSE), ""Comp Status"", ""Maint"", ""ManAuto"", ""Auto"")0, ""Auto Maint"", ""Eval This Row""))), IF(INDIRECT(""RC8"", FALSE)=""Run Auto"", """", """"))" Range("I3").Select I entered it the way you suggested. I have to confess though, I've been relying heavily on the macro recorder, so can you tell me what you mean by "option explicit"? Thanks. "Peter T" wrote: Maybe I misunderstood your problem. Try this manually in say A1 ="ABC" & 123 To write the same formula in VBA you need the double quotes I mentioned, like this Range("A3").Formula = "=""ABC"" & 123" With a simple formula it's easy enough to include those extra quotes. However with your big formula probably better with a bit of help along the lines I suggested. If this is not the issue get back and I'll have another look. But show us your actual code, ie what you have in the VBE. Head the module Option Explicit and declare your variables. State which line an error occurs, or the incorrect resultant formula vs expected formula. Regards, Peter T |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Where has my typing bar gone? | Setting up and Configuration of Excel | |||
| Get #### when typing | Excel Discussion (Misc queries) | |||
| name typing | Excel Worksheet Functions | |||
| Is there a better way (with less typing)? | Excel Programming | |||
| Is there a better way (with less typing)? | Excel Programming | |||
Hybrid Mode
