1

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
Improve this question
2
  • Are the values in your cells numeric, or text? If they're not numeric then you need ActiveCell.Formula = "=""" & ActiveCell.Value & """" Commented Feb 14, 2014 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.. Commented Feb 14, 2014 at 23:01

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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
Improve this answer

2 Comments

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
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...
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
Improve this answer

1 Comment

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.