INDEX functions problem

  • Thread starter Thread starter Nader
  • Start date Start date
N

Nader

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


| 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

| 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
|
|
|| 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.
 
Replace the semi colon in your formula with a comma.

Worksheets("Sheet1").Range("F2").Value = "INDEX(A1:E20*-,-*-4;4)"
Worksheets("Sheet1").Range("F2").Value = "INDEX(A1:E20;4;4)"
Click to expand...
 
Replace the semi colons in your formula with commas.

Worksheets("Sheet1").Range("F2").Value = "INDEX(A1:E20*-,-*-4*-;-*-4)"
Worksheets("Sheet1").Range("F2").Value = "INDEX(A1:E20;4;4)"
Click to expand...
 
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)"
Worksheets("Sheet1").Range("F2").Value = "INDEX(A1:E20;4;4)"
Click to expand...
 
Back
Top