Thread: Assigning a formula to a cell within a macro

I need to assign a formula to a cell inside a macro.
The macro loops through a number of rows doing various things. At one point I need to put a formula in Cell A in that row. As a test I have used

    Worksheets("Import").Cells(rngCell.Row, 1) = "FAST"

And this works,a assigning the value 'Fast' to cell A on the row specified.

I need the formula as follows (written as if I was entering it directly into a cell, but with 'x' as the row number) in cell A on that row

i=if(Nx<.Cx+Ex+Fx,"ERROR","")

can anyone help me out please.

Hi TSR,

This is a macro you could record fairly easily. I wrote the formula in a cell, turned on the macro recorder, pressed F2 (i.e., pretend I want to edit the formula), then turned off the macro recorder.

Actually, I did this 3 times to show how using $ (fixed cell references) would change the result.

The first recorded formula (no $) gave me this code:
ActiveCell.FormulaR1C1 = "=IF(RC[5]<(RC[-6]+RC[-4]+RC[-3]),""Error"", """")"
This formula is only good if you put the formula in the same column that I did.


The second time I used $ for both row and column:
ActiveCell.FormulaR1C1 = "=IF(R8C14<(R8C3+R8C5+R8C6),""Error"", """")"
This formula is only good if you put the formula in row 8 every time.

The first time I used $ to fix the columns, but let the rows float:
ActiveCell.FormulaR1C1 = "=IF(RC14<(RC3+RC5+RC6),""Error"", """")"
This is the formula you want.

when you refer to 'Cells(row, column)' and leave it blank afterwards (i.e. not using Cells(row, column).Value, Cells(row, column).Text, etc) it assumes that you are entering only a direct, typically general format, text or integer into the cell.

I hate using R1C1, so if you'd like to use just .formula, this is how....

Worksheets("Import").Cells(rngCell.Row, 1).Formula = "=IF(N" & x & "< C" & x & "+E" & x & "+F" & x & ", ""ERROR"", """" )"

thanks guys - I'll give it a go and let you know
(oh - I'm a poet and i didnt know it)

magic - problem solved

If you like the response and feel your problem has been solved, edit your original post and mark it as solved please. thank you =)

will do - but how?