VBA to insert a formula into a cell

[AusSteelMan]

Hi everyone,

I have a question about the .Formula command. I have a macro which works correctly, so my question is actually about my understanding/learning and cleaning my code up: not making the code work.

Quick scene-setting:
I have a long formula that concatenates several pieces of text from a range of cells. If the user wants to insert a row into the worksheet, they run a marco. This macro ensures all the formulas are inserted cleanly in the right places.

For simplicity, I have shortened 1 of these forumla build codes.

Cells(IRN, 12) = "=BA" & IRN & "&$BG$2&BB" & IRN

IRN= the row number of the active cell
So this code inserts a formula into the 12th column (column L) and row IRN

If I use the .Formula syntax, I can get it to work if it looks like this:

Cells(IRN, 12).Formula = "=BA" & IRN & "&$BG$2&BB" & IRN

Basically just added ".Formula"

Questions:
1. Is one method preferred over another and/or are there downfalls of one over the other?
2. Is there a "cleaner" way of writing the .Formula version? I don't know how to incorperate the IRN without concatenating string of text. Any examples I've seen look something like

[FONT=WileyCode-Regular][SIZE=2][FONT=WileyCode-Regular][SIZE=2]Range(“A13”).Formula = “=SUM(A1:A12)”[/SIZE][/FONT][/SIZE][/FONT]

which could easily have the IRN used to determine the cell location as such:

[FONT=WileyCode-Regular][SIZE=2][FONT=WileyCode-Regular][SIZE=2]Cells(IRN, 12).Formula = “=SUM(A1:A12)”[/SIZE][/FONT][/SIZE][/FONT]

but I don't know how else, if possible, to include IRN into the actual formula.

Like I said above, this is only for my knowledge and in an attempt to clean up code.

Any thoughts are greatly appreciated.

Thanks,
Darren

 

[Weaver]

You could use r1c1 notation instead

Cells(IRN, 12).Formula = "=BA" & IRN & "&$BG$2&BB" & IRN

becomes

Cells(IRN, 12).FormulaR1C1 = "=rc53 &r2c59&rc54"

 

[AusSteelMan]

Hi Weaver,

Thanks for your suggestion - it worked well of course. Sorry for the delayed response too.

One question.
If the R in RC53 is not stipulated, is the currently selected row number used?
That is the only thing that I can come up with to explain not needing to use "IRN" in the formula build.

(I find that I don't use R1C1 style very often, so maybe this question is just a basic principle)

I will have a go at re-writing some of my code to see if I can tidy it up while maintaining the fact that it actually works now (maybe I am fixing something that isn't broken!!)

Thanks again,
Darren

 

[Weaver]

That's correct.

It comes in handy as you can populate whole ranges with formulas in one go.

sub test
  range("A1:A20").formula="=row()"
  range("B1:B20").formulaR1C1="=rc[-1]*10"
  range("C1:C20").formulaR1C1="=rc1*10"
end sub

run this code on a clean sheet and have a look at the results