CODEWITHSUNDEEP
vbaexcel
Joe Cross
Joe Cross

Reputation: 53

Evaluate text string as formula in VBA

I am using VBA to enter a formula in a column I have inserted into a downloaded data set. Apparently the data set I am inserting into is stored as text, so the inserted column inherits that characteristic. I want to be clear, it is not formatted as text, it has a typename of text.

I have =2+2, and the cell displays =2+2 instead of evaluating the formula and displaying 4. From some other suggestions, I tried

activecell.value = Evaluate(activecell.value)

That worked, and the cell would then display 4. But the formula is now gone, and the cell's formula = 4. I need the formula to stay for the purpose of using the autofill feature.

I could step through with a loop in VBA, but that seems over complicated. Is there a simple way to force excel to stop assuming the data type is text?

Upvotes: 3

Views: 2147

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

If both the Formula Bar and the cell show:

=2 + 2

Then this should fix the situation:

Sub qwerty()
    With ActiveCell
        .NumberFormat = "General"
        .Formula = .Value
    End With
End Sub

enter image description here

Upvotes: 3

Related Questions