Enter Cell Formula using VBA

Question

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?

Thanks

Solution

Barry and Excellll have clearly solved your specific question. As to the more general, I've googled around, and was surprised not to find a good general tutorial on this. Here's a short post from Daily Dose of Excel.

I'd turn on the macro recorder, create some formuals, 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.