Formula added via VBA not calculating (need to edit & {enter} to correct) - XL2007

[AOB]

Hi all,

Having issues with the below code in Excel2007, which applies basic formulae to 4 variable-defined ranges. For some reason, which I cannot figure out despite trawling various forums for similar issues, the formula applied to column D (2nd line) does not calculate (cells remain blank) - the others populate the expected values exactly as expected.

However, if I edit the formula (i.e. click in the formula bar) and then hit {enter}, the value appears. Autofilling the 'new' (albeit unchanged) formula downward, fixes the problem for the whole range, and the dependant values in column F (4th line) also update as expected.

So the basic formula syntax seems to be fine; Excel just doesn't perform the calculation automatically for this particular formula/range.

I should point out that the Calculation settings for the workbook are set to 'Automatic' and all of the cells on 'wsReference' are pre-formatted to 'General'.

wsReference.Range("C2:C" & lgLastRow).FormulaR1C1 = "=RIGHT(RC[-1],3)"
wsReference.Range("D2:D" & lgLastRow).FormulaR1C1 = "=IFERROR(IF(MATCH(RC[-1],Currencies!A:A,0)>0,RC[-1],""""),"""")"
wsReference.Range("E2:E" & lgLastRow).Value = "NOST"
wsReference.Range("F2:F" & lgLastRow).FormulaR1C1 = "=IF(RC[-2]="""",RC[-4],LEFT(RC[-4],LEN(RC[-4])-LEN(RC[-2])))"

Any suggestions? I'm absolutely stumped and can't find an explanation anywhere!

Thanks in advance!

Al

(P.S. Detail if needed : lgLastRow is a long variable defining the last populated row of the spreadsheet. Using row 2 as an example, C2 takes the rightmost 3 characters of B2. D2 then uses the MATCH function to determine if those 3 characters are a recognised currency code ('Currencies' sheet); if it finds a match it returns the same 3 characters, if not it leaves the cell blank. If D2 is blank, F2 takes the value in B2, otherwise it takes the value in B2 with these last 3 characters removed)

 

[Richard Schollar]

Hi

You are mixing and matching formula styles which causes the problem - amend that line to:

wsReference.Range("D2:D" & lgLastRow).FormulaR1C1 = "=IFERROR(IF(MATCH(RC[-1],Currencies!C1,0)>0,RC[-1],""""),"""")"

Amendment is in Red.

 

[AOB]

I'm as delighted to have that pointed out to me as I am disappointed in myself for not spotting it earlier!!

Thanks a million Richard, hugely appreciated!!

Have a good weekend!