Run Time Error, FormulaR1C1

[christian_lexander]

Hi,

I get Error 1004 all the time and i don´t know what´s wrong.

My goal is to get the position of the last text in a column.

Worksheets("Transaktioner").Range("A1").FormulaR1C1 =
"=IF(ISERROR(CELL(""address"";INDEX(Transaktioner!K:K;MATCH(REPT(""z"";
255);Transaktioner!K:K;1);1)));0;CELL(""address"";INDEX(Transaktioner!
K:K;MATCH(REPT(""z"";255);Transaktioner!K:K;1);1)))"

The formula works when i paste it to my sheet. And i have replaced the
single " with "".

My first post here. I hope some kind person can help me.

Christheburg

 

[Jim Cone]

It worked for me after replacing the semi-colons with commas.
Also, as posted, the code needs an underscore following the first equal sign...
= _
You also might try changing .FormulaR1C1 to .Formula
However, it worked both ways for me.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



<[email protected]>
wrote in message
Hi,
I get Error 1004 all the time and i don´t know what´s wrong.
My goal is to get the position of the last text in a column.
Worksheets("Transaktioner").Range("A1").FormulaR1C1 =
"=IF(ISERROR(CELL(""address"";INDEX(Transaktioner!K:K;MATCH(REPT(""z"";
255);Transaktioner!K:K;1);1)));0;CELL(""address"";INDEX(Transaktioner!
K:K;MATCH(REPT(""z"";255);Transaktioner!K:K;1);1)))"
The formula works when i paste it to my sheet. And i have replaced the
single " with "".
My first post here. I hope some kind person can help me.
Christheburg

 

[Dave Peterson]

First, VBA is USA centric.

You'll want to change all the semicolons to commas. They'll be converted when
the formula is plopped into the worksheet cell.

Second, your formula doesn't look like it's in R1C1 reference style. It looks
like A1 reference style to me.

I'd try:

Worksheets("Transaktioner").Range("A1").Formula _
= "=IF(ISERROR(CELL(""address""," _
& "INDEX(Transaktioner!K:K,MATCH(REPT(""z"",255)" _
& ",Transaktioner!K:K,1),1))),0," _
& "CELL(""address"",INDEX(Transaktioner!K:K," _
& "MATCH(REPT(""z"",255),Transaktioner!K:K,1),1)))"

 

[christian_lexander]

First, VBA is USA centric.

You'll want to change all the semicolons to commas.  They'll be converted when
the formula is plopped into the worksheet cell.

Second, your formula doesn't look like it's in R1C1 reference style.  Itlooks
like A1 reference style to me.

I'd try:

Worksheets("Transaktioner").Range("A1").Formula _
   = "=IF(ISERROR(CELL(""address""," _
     & "INDEX(Transaktioner!K:K,MATCH(REPT(""z"",255)" _
     & ",Transaktioner!K:K,1),1))),0," _
     & "CELL(""address"",INDEX(Transaktioner!K:K," _
     & "MATCH(REPT(""z"",255),Transaktioner!K:K,1),1)))"













--

Dave Peterson- Dölj citerad text -

- Visa citerad text -

It work´s perfect !

Thank´s a lot !

Christheburg