Correct referencing - Excel vb

[ItIsHardToProgram]

Why does the following not work???

Workbooks("Analyse évaluations 2008.xls").Worksheets("Cascades").Cells(17, 9).Value

Can you only reference a cell on a active workbook?????

=/

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Is that workbook OPEN in the same instance of Excel?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Yes it is opened on the same instance of excel

but it isnt passed exactly like that.

Sub EntryEVA()

Dim ws As Worksheet
Dim WSEVAL() As String
Dim iSheetcount As Integer
Dim iSheet As Integer

On Error GoTo ErrorHandler

ErrorHandler:
    If MsgBox("Skip this error????" & Error(Err) & " " & Err, vbYesNo) = vbYes Then
        'Exit Sub
    End If

iSheetcount = Workbooks("SommaireProForma2008.xls").Worksheets.Count
ReDim WSEVAL(iSheetcount)

For iSheet = 2 To iSheetcount
   
    WSEVAL(iSheet) = Workbooks("SommaireProForma2008.xls").Worksheets(iSheet).Name
    Debug.Print WSEVAL(iSheet)
    Workbooks("Analyse évaluations 2008.xls").Activate
    Debug.Print Worksheets(WSEVAL(iSheet)).Cells(17, 9).Value
    
    'Debug.Print Workbooks("SommaireProForma2008.xls").Worksheets(iSheet).Cells(5, 22).Value
Next iSheet



End Sub

I can't seem to be getting any value off of my references, im still testing, there is value in cells etc... I tried formulaR1C1, im stunned right now, usualy this works fine.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

        Debug.Print WSEVAL(iSheet)
'  DON'T need to ACTIVATE!!!Workbooks("Analyse évaluations 2008.xls").Activate
        Debug.Print Workbooks("Analyse évaluations 2008.xls").Worksheets(WSEVAL(iSheet)).Cells(17, 9).Value

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Sigh, yes I was doign that...

Further testing now I feel quite dumb, I was checking at 17,9 when I shouldve been checking at 9,17.

This should teach me, always look for the simplest things, thanks any way.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Ok this is the line it errors on...

Workbooks("SommaireProForma2008.xls").Worksheets(WSEVAL(iSheet)).Cells(22, 5).Value

It says the property isnt managed by the specific object, why is that?is it in the formating of the cell?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Do you have the EXACT same sheet names in both workbooks?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Yes, wich is the logic behind this, I used to reference it with Worksheets(isheet) but i decided it dint mather + I wanted to see if that was causing the problem, well it wasnt.

There used to be a certain link in that specific cell, but even after removing that link its not working, and other cells are referencing fine on the same workbook....

Quite confusing....

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

The error number is "438"

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Got it, sheet was protected, even though I could change somethings.

=/

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.