Run Time Error, FormulaR1C1

  • Thread starter Thread starter christian_lexander
  • Start date Start date
C

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
 
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
 
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)))"
 
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 -
Click to expand...

It work´s perfect !

Thank´s a lot !

Christheburg
 
Back
Top