Inserting a formula into a cell using a macro

[MoonWeazel]

Hi Chaps,

I have a spreadsheet which needs to be linked to another spreadsheet.

I have written a macro to insert all the links from one sheet to another when you add a new line.

Most of it is just cell refs but there are some formula too.

Here is some of the code I currently have:

ActiveCell.Offset(0, 26).Value = "=(IF(AU3="G","Green",IF(AU3="A","Amber",IF(AU3="H","On Hold","Red"))))"

I would like to stick to the A1 notation rather than the R1C1 and I would like the formula inserted as the value of the cells will change.

TIA,

Lou

[Carim]

Hi,

Use the instruction

Please Login or Register  to view this content.

[MoonWeazel]

Hi Carim,

Thanks for the prompt response!

I need to use the activecell.offset as i found this easier than moving the activecell and having to select it and then define the value and then move again!

Is there anyway of combining the .formula with the offset function?

TIA,

MoonWeazel

[Carim]

Yes ...

Please Login or Register  to view this content.

[MoonWeazel]

Hi Carim,

Thanks again!

When I entered the line...

=IF(AU4="G","Green",IF(AU4="A","Amber",IF(AU4="H","On Hold","Red")))

I get a syntax error message and a compile error with the "G" highlighted.

Any helps is very groovily accepted!!!

Ta!

MW

[Carim]

Hi again,

For quotes to appear, you need to double them ...
i.e. ""...""

[MoonWeazel]

am being a bit thick!!!

i have done as you suggested but it is still coughing up errors!

now i get an expected expression error:

here is the line of code:

Activecell.Offset(0,26).Formula = ""=IF(AU4="G","Green",IF(AU4="A","Amber",IF(AU4="H","On Hold","Red")))""

please help - i think that i am getting a headache from staring at the screen for hours!

MW

[Carim]

Hi again ...

Should have explained better ...

Please Login or Register  to view this content.