error in active chart - why?

[earp_]

Hi,
in sheet2 i have some cells which are update dynamically, and in sheet3 i have a chart which is updated by values that are in sheet2

everything works good if i'm on sheet2 (sheet2 active)

but if i click on sheet3 and i wait for the IF case i've got an error.
why?
local Variable

Sub macro1()
Dim pIndex As String

pIndex contains A after a while B and so on...

...
If WorksheetFunction.CountA(Sheets("Sheet2").Columns(pIndex)) > 1

I have no problem also if i'm in another sheet that is not a chart

 

[Richard Schollar]

An interesting quirk with VBA! Much like one a friend told me about the other day:

You probably know that you can't name a sheet "history" as it is a reserved word? Well you can if you have history as a field value in a pivot table and have this field as a Page field and select "Show Pages" - you'll get a sheet named "history".

 

[RoryA]

How bizarre! And if you double-click on the history tab to edit the name and just press Enter, you get an error, but pressing Escape is ok. I guess it doesn't check the name if you cancel.

 

[Jon von der Heyden]

An interesting quirk with VBA! Much like one a friend told me about the other day:

You probably know that you can't name a sheet "history" as it is a reserved word? Well you can if you have history as a field value in a pivot table and have this field as a Page field and select "Show Pages" - you'll get a sheet named "history".

What on earth would have possessed you to try that out?

 

[earp_]

hi guys,
i have the same problem with this line

wbTis.Worksheets("sheet1").Range("F" & myColCheck & ":" & "V" & myColCheck).FormulaR1C1 = wbTis.Worksheets("sheet1").Range("F11:V11").FormulaR1C1

when the active sheet is a chart the formula doesn't work and it puts a - simbol.
any idea?

 

[Andrew Poulsom]

I had no problem with this code when a Chart sheet was active:

Sub Test()
    Dim wbTis As Workbook
    Dim myColCheck As Long
    Set wbTis = ThisWorkbook
    myColCheck = 12
    wbTis.Worksheets("sheet1").Range("F" & myColCheck & ":" & "V" & myColCheck).FormulaR1C1 = wbTis.Worksheets("sheet1").Range("F11:V11").FormulaR1C1
End Sub

What is your formula?

 

[earp_]

That is realy weird.
Maybe because for activating the macro i use this

wbTis.SetLinkOnData "FDF|Q!'TIS;timestamp'", "shareTis"

then in shareTis if the event is verified and the active sheet is a chart
in my cell instead of having

=+F41*(1-F$5)+$E42*F$5

i have got this

=+A65536*(1-A$5)+$E1*A$5

which is wrong.

Everything works perfect if i'm not into the chart sheet when the event is verified.
I can use a button to activate it

wbTis.Worksheets("sheet1").Range("F" & myColCheck & ":" & "V" & myColCheck).FormulaR1C1 = wbTis.Worksheets("sheet1").Range("F11:V11").FormulaR1C1

so i'm not in the chart sheet, and it works.
But this is what i wouldn't.

 

[Andrew Poulsom]

I what cells are the 2 formula you posted?

 

[earp_]

F42

If myColCheck = 42

wbTis.Worksheets("sheet1").Range("F" & myColCheck & ":" & "V" & myColCheck).FormulaR1C1 = wbTis.Worksheets("sheet1").Range("F11:V11").FormulaR1C1

I should get this

=+F41*(1-F$5)+$E42*F$5

instead i got this

=+A65536*(1-A$5)+$E1*A$5

A5 is empty and E1 is empty.
I put just the formula of F42 there are other formulas from F to V

 

[Andrew Poulsom]

What is the formula in F11?

 

[earp_]

in F11
=+F10*(1-F$5)+$E11*F$5

and in F10
=+$E10

in E11 a value 1.6
in E10 a value 1.7