0

How would I go about turning the excel formula =B4/B5 into vba code? So far, this is my code which does not work of course: 

ActiveCell.FormulaR1C1 = "=B4/B5"

As you can tell, I am not very familiar with R1C1. If needed, I have created a function Col_Letter() which takes a number and returns the letter of the corresponding column. Any help would be greatly appreciated!

FreeMan
  • 5,660
  • 1
  • 27
  • 53
Paradox
  • 4,602
  • 12
  • 44
  • 88
  • 6
    `ActiveCell.Formula = "=B4/B5"` – Siddharth Rout May 29 '15 at 14:03
  • That fixed it! Thanks for your help. – Paradox May 29 '15 at 14:05
  • Y not use answer, even for such a simple solution? That way the subject would seem solved once OP has accepted answer. It is 15 free reputation points! ;) – R3uK May 29 '15 at 14:18
  • 2
    to use `.FormulaR1C1` you would use `=R2C4/R2C5`. [@SiddharthRout](http://stackoverflow.com/questions/30531634/excel-formula-to-vba-code#comment49136765_30531634)'s solution is better, though. – FreeMan May 29 '15 at 14:22
  • I was wondering the same thing but figured Sid had it right since he's been around the SO block 1M more times than me. I'm curious to learn more about how to best balance comments vs answer. – puzzlepiece87 May 29 '15 at 14:22
  • 1
    I decided to expand the comment into an answer to ensure that this question does not end up on the 'unanswered' page. – Dennis Jaheruddin May 29 '15 at 14:25
  • 1
    @pnuts Thx for explanation, I forgot that as I'm personally running toward 2k rep to be able to revise edits! – R3uK May 29 '15 at 14:33

1 Answers1

3

As pointed out in the comments by @siddarth you are looking for:

ActiveCell.Formula = "=B4/B5"

If you are in a hurry and just need to make the macro work, a compromise on code readability may be acceptable. In that case you could do the following:

  1. Start recording a macro
  2. Do EXACTLY what you need to do
  3. Stop recording and extract the line from the macro code

Note that this will work for nearly everything, but may lead to somewhat confusing code.

Dennis Jaheruddin
  • 21,208
  • 8
  • 66
  • 122
  • 2
    The macro recorder is a great way to learn! It can teach some bad programming habits, though, so read [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) if you're going to do that. – FreeMan May 29 '15 at 14:24