| 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Typing vs VBA
OK I get the error you mentioned, start by rearranging like this -
Sub test() Dim sFmla As String Dim rCell As Range Set rCell = Range("I2") sFmla = "=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"", """", """"))" rCell.Formula = sFmla End Sub The formula is the same as yours though you'll probably need to unwrap The main change is Formula vs your FormulaR1C1 That at least enables the formula to be entered though I have no idea if it will give the correct result, it's impossible for me to recreate your workbook. At a glance though "RC8" & RC1 don't look right, would expect a numeral after the R. Notice there's no need to 'Select' the cell (you'd need to qualify it further if it's not on the activesheet) You didn't need Option Explicit as you didn't use any variables. But if you use them, as in my example, best to head the module with Option Explicit, ie include it at the top of the module. Regards, Peter T "JG" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Typing vs VBA
Ding ding! We have a winner. That worked.
Bonus question(s).... Why? Why can I key this, but not record it? Why can't I enter this directly into the VBE...why do I have to assign it to a variable first? "Peter T" wrote: OK I get the error you mentioned, start by rearranging like this - Sub test() Dim sFmla As String Dim rCell As Range Set rCell = Range("I2") sFmla = "=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"", """", """"))" rCell.Formula = sFmla End Sub The formula is the same as yours though you'll probably need to unwrap The main change is Formula vs your FormulaR1C1 That at least enables the formula to be entered though I have no idea if it will give the correct result, it's impossible for me to recreate your workbook. At a glance though "RC8" & RC1 don't look right, would expect a numeral after the R. Notice there's no need to 'Select' the cell (you'd need to qualify it further if it's not on the activesheet) You didn't need Option Explicit as you didn't use any variables. But if you use them, as in my example, best to head the module with Option Explicit, ie include it at the top of the module. Regards, Peter T |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Typing vs VBA
In this particular case I don't know why the recorded macro did not work, I
can't recreate the setup to test. It's not necessary first to assign the formula to a variable, it just makes things easier. Recorded macros merely record the results of keystrokes, including unnecessary select and activate. Generally it's better to adapt a macro after recording it. Regards, Peter T "JG" wrote in message ... Ding ding! We have a winner. That worked. Bonus question(s).... Why? Why can I key this, but not record it? Why can't I enter this directly into the VBE...why do I have to assign it to a variable first? "Peter T" wrote: OK I get the error you mentioned, start by rearranging like this - Sub test() Dim sFmla As String Dim rCell As Range Set rCell = Range("I2") sFmla = "=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"", """", """"))" rCell.Formula = sFmla End Sub The formula is the same as yours though you'll probably need to unwrap The main change is Formula vs your FormulaR1C1 That at least enables the formula to be entered though I have no idea if it will give the correct result, it's impossible for me to recreate your workbook. At a glance though "RC8" & RC1 don't look right, would expect a numeral after the R. Notice there's no need to 'Select' the cell (you'd need to qualify it further if it's not on the activesheet) You didn't need Option Explicit as you didn't use any variables. But if you use them, as in my example, best to head the module with Option Explicit, ie include it at the top of the module. 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 | |||
Linear Mode
