Typing vs VBA

[JG]

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.

 

[Jay]

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]

There are only 5 in that function, and I thought it was 7 nested IFs was the
limitation.

Thanks though.

 

[Peter T]

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]

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]

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]

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]

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]

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]

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