VBA index

[ThomasOES]

How can this code be fixed to return the value from Row 6 Column 5 from Workbook "C S O N H Input", Sheet1, Range("A1:P1000")?

ActiveCell.Offset(0, 3).Value = WorksheetFunction.Index([C S O N H Input.xlsx].Sheets("Sheet1").Range("A1:P1000"), 6, 5)

Thanks

Tom

 

[DanteAmor]

The index function is to obtain a value based on a row and column intersection, but if you already have the row and the column then it can be directly like this:

    ActiveCell.Value = Workbooks("C S O N H Input.xlsx").Sheets("Sheet1").Cells(6, 5).Value

Or also like this

ActiveCell = WorksheetFunction.Index(Range("'[C S O N H Input.xlsx]Sheet1'!A1:P1000"), 6, 5)

 

[ThomasOES]

I do have the column, but the row depends on a name in column A. I can use match to get the row number.
Would look something like this

[LEFT][COLOR=#333333][FONT=monospace]ActiveCell.Offset(0, 3).Value = WorksheetFunction.Index([C S O N H Input.xlsx].Sheets("Sheet1").Range("A1:P1000"), WorksheetFunction.Match[LEFT][COLOR=#333333][FONT=monospace]([C S O N H Input.xlsx].Sheets("Sheet1").Range("A1:A1000"))[/FONT][/COLOR][/LEFT]
, 5)[/FONT][/COLOR][/LEFT]

Of course the above code is off, but I think it would similar.

Thanks
Tom

 

[DanteAmor]

Change "name" for the data to look for in column A

    fila = WorksheetFunction.Match("name", Range("'[C S O N H Input.xlsx]Sheet1'!A1:A1000"), 0)
    ActiveCell = WorksheetFunction.Index(Range("'[C S O N H Input.xlsx]Sheet1'!A1:P1000"), fila, 5)

 

[ThomasOES]

Keep getting error. Even when I run only

[LEFT][COLOR=#333333][FONT=monospace]ActiveCell = WorksheetFunction.Index(Range("'[C S O N H Input.xlsx]Sheet1'!A1:P1000"), 6, 5)[/FONT][/COLOR][/LEFT]

I'm having success running

ActiveCell.Offset(0, 3).FormulaR1C1 = _
    "=INDEX('[C S O N H Input.xlsx]Sheet1'!R1C1:R1000C16,MATCH(RC[-2],'[C S O N H Input.xlsx]Sheet1'!R1C1:R1000C1,0),5)"

But I'd prefer the worksheetfunction method
I need greater flexibility for a very large program

Thanks
Tom

 

[DanteAmor]

Change "name" for the data to look for in column A

    fila = WorksheetFunction.Match("name", Range("'[C S O N H Input.xlsx]Sheet1'!A1:A1000"), 0)
    ActiveCell = WorksheetFunction.Index(Range("'[C S O N H Input.xlsx]Sheet1'!A1:P1000"), fila, 5)

works for me

Keep getting error. Even when I run only

What does the error say?

 

[ThomasOES]

Hello Dante

When I run

ActiveCell.Offset(0, 3) = WorksheetFunction.Index(("[C S O N H Input.xlsx]Sheet1'!A1:P1000"), 6, 5)

The error message is

Run-time error '1004'
Unable to get the Index property of the WorksheetFunction class

 

[ThomasOES]

Dante

As you probably guessed the workbook "C S O N H Input" is a database

When I open the workbook "C S O N H Input" and run the code

ActiveCell = Application.WorksheetFunction.Index(Range("'[C S O N H Input.xlsx]Sheet1'!A1:P1000"), 6, 5)

It works

However if I close the workbook and run the code I then get message

Method 'Range' of object '_Global' failed

I don't understand why the workbook must be open for the code to work.
The other formula R1C1 code works with the database workbook closed.

 

[DanteAmor]

ActiveCell = Application.WorksheetFunction.Index(Range("'[C S O N H Input.xlsx]Sheet1'!A1:P1000"), 6, 5)

It works
However if I close the workbook and run the code I then get message
Method 'Range' of object '_Global' failed

To have the result online, the book must be open.

-----

I don't understand why the workbook must be open for the code to work.
The other formula R1C1 code works with the database workbook closed.

With the formula R1C1, say "it works" but you do not have the result online, the formula is latent but with the previous result, when you open the book the result will actually be updated.