CODEWITHSUNDEEP
excelvbaquotesformula
Alaa Elwany
Alaa Elwany

Reputation: 697

Enter Cell Formula using VBA

I have this simple formula in Cell A1

=10*Round(B1/10,0)

I know how to enter this in VBA, i simply write:

Range("A1").Formula = "=10*Round(B1/10,0)"

Now, what if I don't know the cell whose number I want to round? In other words, I have an integer variable in my code, N, that can take on multiple values, and I want to round the number in row number N of column B.

How do I write this?

I tried:

Range("A1").Formula = "=10*Round(B" & N & "  & "/10",0)"

But this doesn't work. Tried multiple layouts for the quotes but without success.

Can anyone let me know:

  1. How to enter that simple formula? And more importantly
  2. Provide some link/reference that would help me with entering other formulas in the future?

Upvotes: 9

Views: 44992

Answers (2)

Doug Glancy
Doug Glancy

Reputation: 27488

barry houdini said:

You're rounding to the nearest 10? Equivalent formula is =ROUND(B1,-1)

Excellll said:

Range("A1").Formula = "=10*Round(B" & N & "/10,0)"

Here's a short post from Daily Dose of Excel: Worksheet Formulas in VBA Part I

I'd turn on the macro recorder, create some formulas, see what you get, and then google specific issues. Finally, a good book, such as one of John Walkenbach's "Power Programming in VBA" titles should help.

Upvotes: 5

Michael C
Michael C

Reputation: 1

Sub VariableFormula

  N = 5
  Range("A1").Formula = "=10*Round(B" & N & "/10,0)"

End Sub

Upvotes: -2

Related Questions