Run-time error : method failed

[Joris Adriaenssens]

I have written a small macro (to convert an old spreadsheet to Euro)

Sub naar_euro()

Dim myCell As Range
Dim temp As Variant

Set myCell = ActiveCell
temp = myCell.FormulaR1C1
myCell.FormulaR1C1 = "=(" & temp & ")/40.3399"

End Sub

The content of the activecell is a number (1234). When I reach the
line 'temp = ...' I get an error :

Run-time error '-2147319784 (800028018)': Method 'FormulaR1C1' of
object 'Range' failed.


I also tried the method 'Formula', but I got the same result.

What am I doing wrong ?

 

[Jim Rech]

I don't know why your code fails for you. It runs for me.

Does this work?:

temp = myCell.Value

--
Jim Rech
Excel MVP
|I have written a small macro (to convert an old spreadsheet to Euro)
|
| Sub naar_euro()
|
| Dim myCell As Range
| Dim temp As Variant
|
| Set myCell = ActiveCell
| temp = myCell.FormulaR1C1
| myCell.FormulaR1C1 = "=(" & temp & ")/40.3399"
|
| End Sub
|
| The content of the activecell is a number (1234). When I reach the
| line 'temp = ...' I get an error :
|
| Run-time error '-2147319784 (800028018)': Method 'FormulaR1C1' of
| object 'Range' failed.
|
|
| I also tried the method 'Formula', but I got the same result.
|
| What am I doing wrong ?

 

[Joris Adriaenssens]

no, it doesn't work.
When I try to debug and go through the different program-lines step by step,
then I can go with the mouse-cursor over the text 'activecell.value', and
then the value of the cell in the spreadsheet appears in a little box :
'activecell.value = 1050'. The same thing happens when I try
activecell.formula (but then the box shows the formula).

The macro is in the persnlk.xls file (dutch version). When I put the macro
in the workbook I am working on, everything works OK.
I stopped experimenting because I thought the code was wrong, so I only
found out now that I had to put the code in the worksheet it has to work on,
but I want the macro always available.

 

[Jim Rech]

I have no idea why that line would not run in your personal workbook. It
should as far as I can tell. Sorry.

--
Jim Rech
Excel MVP

| no, it doesn't work.
| When I try to debug and go through the different program-lines step by
step,
| then I can go with the mouse-cursor over the text 'activecell.value', and
| then the value of the cell in the spreadsheet appears in a little box :
| 'activecell.value = 1050'. The same thing happens when I try
| activecell.formula (but then the box shows the formula).
|
| The macro is in the persnlk.xls file (dutch version). When I put the
macro
| in the workbook I am working on, everything works OK.
| I stopped experimenting because I thought the code was wrong, so I only
| found out now that I had to put the code in the worksheet it has to work
on,
| but I want the macro always available.
|
|
| | > I don't know why your code fails for you. It runs for me.
| >
| > Does this work?:
| >
| > temp = myCell.Value
| >
| > --
| > Jim Rech
| > Excel MVP
| > | > |I have written a small macro (to convert an old spreadsheet to Euro)
| > |
| > | Sub naar_euro()
| > |
| > | Dim myCell As Range
| > | Dim temp As Variant
| > |
| > | Set myCell = ActiveCell
| > | temp = myCell.FormulaR1C1
| > | myCell.FormulaR1C1 = "=(" & temp & ")/40.3399"
| > |
| > | End Sub
| > |
| > | The content of the activecell is a number (1234). When I reach the
| > | line 'temp = ...' I get an error :
| > |
| > | Run-time error '-2147319784 (800028018)': Method 'FormulaR1C1' of
| > | object 'Range' failed.
| > |
| > |
| > | I also tried the method 'Formula', but I got the same result.
| > |
| > | What am I doing wrong ?
| >
| >
|
|

 

[Joris Adriaenssens]

Jim,

When I make a new workbook, and place the macro in it, it works. So I
placed the new workbook in the xlstart-directory.
I think the macro doesn't work, because the original personal.xls file
is made with a Dutch version of Excel, while I am working now with an
English version of Excel. The original macros in the workbook work
fine, but when I try to put a new macro in the 'Dutch' workbook, it
doesn't work. (I have seen this once before, but thought it was my
macro)

Thank you for the replies : they stimulated to continue searching.
Thank you

Joris Adriaenssens.