Excel visual basic 1004 error, method 'value' of object 'range' failed

Question

I made a little add-in for excel. But for some reason it fails. After a fresh startup of excel it works fine, however when I copy paste text into excel and try to run it, it gives me the error: run-time '1004' , method 'Value' of object 'range' failed.

What I'm trying to do, is quit simple. I like building formula's like : (B5+B6)/2 without the use of an '=' in front so Excel doesn't calculate these expressions. I end up with one big column, and after I am finished I would like to select the first cell of the column with calculations, activate my add-in and he puts an '=' in front and loops downward untill an empty cell. This way each cell in my column is now calculated.

I am lost, can you help me ?

Sub makeFormula()
Do
ActiveCell.Value = "=" & ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.Value <> Empty
end Sub

Are the values in your cells numeric, or text? If they're not numeric then you need `ActiveCell.Formula = "=""" & ActiveCell.Value & """"` — Tim Williams, Feb 14 '14 at 22:43
I think this problem stems from a 'ghost' excel process, and it trying to refer to elements on that. Using activesheet or range or referring to a workbook object seems to break it, as well as using 'With' commands on a workbook or worksheet object. My understanding is you need to be deligant and setting your objects to nothing, but I still have a program that breaks every other run with this error. Use the task manager and kill the excel process and it should run.. — Acantud, Feb 14 '14 at 23:01

score 2 · Answer 1 · answered Feb 15 '14 at 11:38

I've found the solution, with debugging I found out that commas are the problem, So I change the comma to a dot, and then calculate. And now it works like a charm.

Sub makeFormula()
Dim Temp As String
Do
    Temp = ActiveCell.Value2
    Temp = Replace(Temp, ",", ".", 1)
    ActiveCell.Formula = "=" & Temp
    ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.Value2 <> Empty
End Sub

Thanks for all your suggestions.

Tim Williams · Answer 2 · 2014-02-14T23:49:38.003

0

Sub makeFormula()
Dim c as range
Set c = selection.cells(1) 'in case >1 cell is selected
do while len(c.value) > 0
    'need to put quotes around the value if not a number
    'c.Formula = "=""" & c.Value & """"
    'use this if the value is a valid formula without =
    c.Formula = "=" & c.Value 
    Set c=c.Offset(1, 0)
Loop
End Sub

edited Feb 14 '14 at 23:49

answered Feb 14 '14 at 22:47

Tim Williams

154,628
8
97
125

thanks for such a quick answer, but so far no luck, when I try your code nothing happens. Maybe I wasn't clear about my goal. I would like to select a cell and then activate my add-in , this add-in would then include an '=' in front of the cell content so that excel accept it as a function and calculates it. It repeats this with every cell in the column untill it reads an empty cell – ruben Feb 14 '14 at 23:24
It might help if you update your question to be more specific about exact what's in the cells you want to convert into formulas. If you don't need the quotes around the cell value then don't include them... – Tim Williams Feb 14 '14 at 23:25

score 0 · Answer 3 · answered Feb 15 '14 at 00:26

0

You need to pass your value to temporary string variable. Worked for me:

Sub makeFormula()
    Dim Temp As String
    Do
        Temp = ActiveCell.Value2
        ActiveCell.Formula = "=" & Temp
        ActiveCell.Offset(1, 0).Select
    Loop While ActiveCell.Value2 <> Empty
End Sub

answered Feb 15 '14 at 00:26

ttaaoossuuuu

7,786
3
28
58

I tried this one, but still no luck. I copied from another excel file: (2,1+0,3)*3,6 activated the add-in and same error again. Then I changed the comma's to points and it worked... So it seems the commas are the problem – ruben Feb 15 '14 at 08:09

Excel visual basic 1004 error, method 'value' of object 'range' failed

3 Answers3

Linked