FormulaR1C1 Help Please 1

[CWalsh87]

Hi, this is my first post on the forum, although the forums been very helpful over the last few months. I have however searched and cannot find an answer to my question.

I have some VBA code which inserts a formula (using FormulaR1C1) into a cell. the problem is, the formaula is getting inserted with all the correct refences in place, but until i go into the cell, press "F2" then enter, the formula does not calculate.

The cell is formatted in "General"

Here is the VBA code:

Activecell.FormulaR1C1 = "=if(iserror(match(RC[+3],'Product Type'!A:A,0)),"""",RC[+3])"

As i've said, all the references are correct, but until entering the cell, it's as if excel see's the formaula as text.

Thanks in advance!

Chris

 

[xlhelp]

Most likely, you need to go to Tools; Options; Calculation tab and set to Automatic

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.

 

[CWalsh87]

Thanks for the fast reply.

I forgot to mention the calculation setting is on automatic. i've even inserted the xlcaulcationautomatic before the formula is inserted to ensure this.

Chris

 

[SkipVought]

However, if you find that you get delays each time you make changes to your sheet as your sheet automatically calculates, you might want to set the CALCULATE to MANUAL, and then hit F9 (Caclulate) whenever you need to recalculate.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

It seems that excel does not like mixed formulas or '+'sign, or needs capitals. This works:

ActiveCell.FormulaR1C1 = "=IF(ISERROR(MATCH(RC[3],'Product Type'!C[-8],0)),"""",RC[3])"
or for fixed A:A column:
ActiveCell.FormulaR1C1 = "=IF(ISERROR(MATCH(RC[3],'Product Type'!C1,0)),"""",RC[3])"

combo

 

[CWalsh87]

I've narrowed it down slightly.

It has to be something to do with the MATCH() part of the formula. I've deleted the match and put in:

activecell.formular1cq "=if(rc[1]=""AD0001"",""Yes"",""No"")"

And this returns "Yes" or "No" depending on the target cell value.

Any ideas?

 

[CWalsh87]

Here's another thing i've found...

the value returned in the target cell is always what is in the "if_false" part of the "If" formula.

e.g. i've just changed the original code to:

Activecell.FormulaR1C1 = "=if(iserror(match(RC[+3],'Product Type'!A:A,0)),""123"",RC[+3])"

and "123" is displayed in every cell. Sugesting the logic part of the "if" statement (iserror) it true, even though it isn't.

Has anyone else ever had this issue? I'm running out of things to try!

 

[CWalsh87]

Thanks Combo, the second part of your post sorted it!!!!

(missed your comment!)

Really helped me out!

Cheers
Chris