Hello,

I'm trying to use the INDEX fonction in vba and I keeping getting errors.
Here's an example :

Worksheets("Sheet1").Range("F2").Value = "INDEX(A1:E20;4;4)

or

Worksheets("Sheet1").Range("F2").Formula = "INDEX(A1:E20;4;4)

When running one of these codes, I get this error :

Run-time error 1004
Application-defined or object-defined error


What should I do to make it work ?

thanks in advance.

Nader

Hi Nader,

Use

Worksheets("Sheet1").Range("F2").FormulaLocal = "INDEX(A1:E20;4;4)

Or use commas instead of semicolons, then you can use .Formula

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Nader" wrote in message ...
| Hello,
|
| I'm trying to use the INDEX fonction in vba and I keeping getting errors.
| Here's an example :
|
| Worksheets("Sheet1").Range("F2").Value = "INDEX(A1:E20;4;4)
|
| or
|
| Worksheets("Sheet1").Range("F2").Formula = "INDEX(A1:E20;4;4)
|
| When running one of these codes, I get this error :
|
| Run-time error 1004
| Application-defined or object-defined error
|
|
| What should I do to make it work ?
|
| thanks in advance.
|
| Nader
|
|

And of course add a " to the end of the statement

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" wrote in message ...
| Hi Nader,
|
| Use
|
| Worksheets("Sheet1").Range("F2").FormulaLocal = "INDEX(A1:E20;4;4)
|
| Or use commas instead of semicolons, then you can use .Formula
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
| "Nader" wrote in message ...
|| Hello,
||
|| I'm trying to use the INDEX fonction in vba and I keeping getting errors.
|| Here's an example :
||
|| Worksheets("Sheet1").Range("F2").Value = "INDEX(A1:E20;4;4)
||
|| or
||
|| Worksheets("Sheet1").Range("F2").Formula = "INDEX(A1:E20;4;4)
||
|| When running one of these codes, I get this error :
||
|| Run-time error 1004
|| Application-defined or object-defined error
||
||
|| What should I do to make it work ?
||
|| thanks in advance.
||
|| Nader
||
||
|
|

depends on whether you want to do the index in vba and return the answer or
put the formula in a cell

to return the value:
with Worksheets("Sheet1")
.Range("F2").Value = Application.INDEX(Range("A1:E20"),4,4)
End with

to put in a formula
Worksheets("Sheet1").Range("F2").Formula = "=INDEX(A1:E20,4,4)"

when you use formula, you need to put the formula in the same format as US
English.

You also have the option of FormulaLocal, but that is less transportable.

--
Rgards,
Tom Ogilvy



"Nader" wrote:

Hello,

I'm trying to use the INDEX fonction in vba and I keeping getting errors.
Here's an example :

Worksheets("Sheet1").Range("F2").Value = "INDEX(A1:E20;4;4)

or

Worksheets("Sheet1").Range("F2").Formula = "INDEX(A1:E20;4;4)

When running one of these codes, I get this error :

Run-time error 1004
Application-defined or object-defined error


What should I do to make it work ?

thanks in advance.

Nader

Replace the semi colon in your formula with a comma.

Worksheets("Sheet1").Range("F2").Value = "INDEX(A1:E20*-,-*-4;4)"

Nader Wrote:
Worksheets("Sheet1").Range("F2").Value = "INDEX(A1:E20;4;4)"



--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=566629

Replace the semi colons in your formula with commas.

Worksheets("Sheet1").Range("F2").Value = "INDEX(A1:E20*-,-*-4*-;-*-4)"

Nader Wrote:
Worksheets("Sheet1").Range("F2").Value = "INDEX(A1:E20;4;4)"



--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=566629

Replace the semi colons in your formula with commas and place an equals
sign inside the quotes.

Worksheets("Sheet1").Range("F2").Value =
"*-=-*-INDEX(A1:E20*-,-*-4*-,-*-4)"

Nader Wrote:
Worksheets("Sheet1").Range("F2").Value = "INDEX(A1:E20;4;4)"



--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=566629

Thanks a lot Niek !

"Niek Otten" a écrit dans le message de news:
...
Hi Nader,

Use

Worksheets("Sheet1").Range("F2").FormulaLocal = "INDEX(A1:E20;4;4)

Or use commas instead of semicolons, then you can use .Formula

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Nader" wrote in message
...
| Hello,
|
| I'm trying to use the INDEX fonction in vba and I keeping getting
errors.
| Here's an example :
|
| Worksheets("Sheet1").Range("F2").Value = "INDEX(A1:E20;4;4)
|
| or
|
| Worksheets("Sheet1").Range("F2").Formula = "INDEX(A1:E20;4;4)
|
| When running one of these codes, I get this error :
|
| Run-time error 1004
| Application-defined or object-defined error
|
|
| What should I do to make it work ?
|
| thanks in advance.
|
| Nader
|
|