Can I insert formulas into my macros?

[Sarah]

I tried recording some formulas into a macro, but this doesn't work. Is it
possible to do so, without using Visual Basic programming? Can it be done
using Visual Basic programming - I am not an expert

 

[Mike H]

Sarah,

You can record formula entry with the macro recorder:-
ActiveCell.Formula = "=SUM(A3:A9)"
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[6]C[-1])"

These 2 formula are the same, they are in B3 and add up A3 to A9. The first
is entered manually and the second is the recorded version of the same thing.

What precisely are you trying to do?


Mike

 

[Sarah]

Hi Mike,

Thanks for your quick answer ! In fact, I tried recording the copying a
formula I created earlier in another workbook (this one below)

=IF(ISERROR(LEFT(F2;FIND(200;F2)-1));F2;(LEFT(F2;FIND(200;F2)-1)))
it replaces a word, with a number after the word starting by 200, by only
the word

because I always have to do this when I create the updated version of the
same report.

Are you saying that it is necessary to insert the formula manually for it to
be recorded by a macro, and that I can not copy the formula from another
worksheet?

Sarah

Sarah,

You can record formula entry with the macro recorder:-
ActiveCell.Formula = "=SUM(A3:A9)"
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[6]C[-1])"

These 2 formula are the same, they are in B3 and add up A3 to A9. The first
is entered manually and the second is the recorded version of the same thing.

What precisely are you trying to do?


Mike

I tried recording some formulas into a macro, but this doesn't work. Is it
possible to do so, without using Visual Basic programming? Can it be done
using Visual Basic programming - I am not an expert

 

[Mike H]

Sarah,

I can see what you formula does but I'm not sure what the question is, take
these 2 examples

Range("F3").Formula =
"=IF(ISERROR(LEFT(F2,FIND(200,F2)-1)),F2,(LEFT(F2,FIND(200,F2)-1)))"

Range("F3").Formula = Sheets("Sheet1").Range("F5").Formula

The first puts you formula into F3 and the second copies your fromula from
F5 to F3
Note i've changed ; to , for my version of Excel

Mike

Hi Mike,

Thanks for your quick answer ! In fact, I tried recording the copying a
formula I created earlier in another workbook (this one below)

=IF(ISERROR(LEFT(F2;FIND(200;F2)-1));F2;(LEFT(F2;FIND(200;F2)-1)))
it replaces a word, with a number after the word starting by 200, by only
the word

because I always have to do this when I create the updated version of the
same report.

Are you saying that it is necessary to insert the formula manually for it to
be recorded by a macro, and that I can not copy the formula from another
worksheet?

Sarah

Sarah,

You can record formula entry with the macro recorder:-
ActiveCell.Formula = "=SUM(A3:A9)"
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[6]C[-1])"

These 2 formula are the same, they are in B3 and add up A3 to A9. The first
is entered manually and the second is the recorded version of the same thing.

What precisely are you trying to do?


Mike

I tried recording some formulas into a macro, but this doesn't work. Is it
possible to do so, without using Visual Basic programming? Can it be done
using Visual Basic programming - I am not an expert