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

 

[RoryA]

You can't use WorksheetFunction if the activesheet is not a worksheet.

 

[earp_]

so what do you suggest me?
I mean if I want to see sometimes the chart without having errors...

 

[RoryA]

Sorry, what I just said is incorrect. It's the combination of Columns and the activesheet not being a worksheet. If you use:

If WorksheetFunction.CountA(Sheets("Sheet2").Range(pIndex & ":" & pIndex)) > 1

you should be OK.

 

[earp_]

it's underlined in red

 

[RoryA]

Do you have the word 'Then' on the end of that line? (I assumed you just left it out of what you posted originally since you said it worked sometimes)

 

[earp_]

ops
thanks

it works perfectly.thanks again

 

[Richard Schollar]

Sorry, what I just said is incorrect. It's the combination of Columns and the activesheet not being a worksheet.

That's interesting Rory - any idea why (I realise this is probably akin to asking why water is wet)?

 

[RoryA]

That's interesting Rory - any idea why ?

None whatsoever! though I am researching it now - it must have come up before, unless I am completely wrong!

(I realise this is probably akin to asking why water is wet)

Not really - I'm sure I could get a £250k grant to research that!

 

[RoryA]

More specifically, the problem seems to be using Columns(column_letter) when the activesheet is not a worksheet. If you use Columns(column_number) then it's fine.