CODEWITHSUNDEEP
vbaexcel
user7433793
user7433793

Reputation: 85

VBA insert a formula to existing cell

Cell A1 refers to Cell A5, which has a value of 5.5. I want to insert say a round function to Cell A1 that reads =round(A5,). I tried the following code below but it's not working as I intend it to.

Sub roundmacro()
   Dim n As String
   n = Range("A1").Value 'returns the value but I need reference from that range object instead  
   Range("A1").Formula = "=round(" & n & ",)"
End Sub

Thanks in advance

Upvotes: 2

Views: 1583

Answers (4)

PSotor
PSotor

Reputation: 346

Wouldn't it be better to do the calculation in VBA, like this:

Sub formulas()

Range("A1").Value = Application.WorksheetFunction.Round(Range("A5").Value, 0) ' You can put any formula after the 'Application.WorksheetFunction.'

End Sub

Upvotes: 2

A.S.H
A.S.H

Reputation: 29352

Range("A1").Formula = "=Round(" & Mid(Range("A1").Formula, 2) & ",)"

This turns any existing formula into a rounding of that formula. So if the initial formula was =A5, the new formula becomes =Round(A5,).

p.s. Works for any initial formula that returns a number (you cannot round a string, naturally).

Upvotes: 4

Rdster
Rdster

Reputation: 1872

So you want the formula in A1 to show "A5" in it?

Range("A1") = "=Round(A5,)"

Upvotes: 2

pokemon_Man
pokemon_Man

Reputation: 902

Works fine for me, A1 = 6 

Sub roundmacro()
   Dim n As String
   n = Range("A5").Value
   Range("A1").Formula = "=round(" & n & ",)"
End Sub

Upvotes: 1

Related Questions