Formula written in Macro not evaluating

[jlt199]

Hi, this is my first time on this forum. I've been muddling through with building macros in Excel, but have found a problem I can't solve.

My macro opens a new workbook, reads in and sorts/filters data from a text file before analysing it.

I've input a formula into cell using the macro, but after the macro has finished the formula isn't working. However, if I then click on the cell containing the formula, go to the formula bar and hit 'enter' the formula then works correctly.

I can't think of an explanation for this and would appreciate some help.

Many thanks

 

[SkipVought]

Hi,

Is your workbook calculation set to MANUAL? Hit F9 to calculate.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jlt199]

Thanks, I've tried that. But it didn't work. All my other formulas are working.

The formula is

=IF(ROW() = $C$15,$B$5+$B$7,IF(INDIRECT(ADDRESS(E8+1,K8,,,D8)) < $C$18,E8,MATCH($C$18,OFFSET(GFDBG!A:A,,K8-1),1)))

If the first if-statement is true then it calculates no problem. It's only when the embedded if-statment is enterd that there is a problem.

 

[SkipVought]

What happens if you the the Formula EVALUATE feature?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jlt199]

It suggests the problem is with

OFFSET(GFDBG!A:A,,K8-1)

at least that is where the #NAME? appears. However, I have used similar code in other formulas and it works fine. Could the problem be with the nested if-statement?

What I don't understand is that when I go to the formula bar for that cell and hit 'enter' it then evaluates correctly. Why would that be?

 

[jlt199]

Ok, I've fixed it!

It was a problem with the OFFSET function, but I still don't really understand why. I have now changed the way the formula is constructed in the macro from

.Range("H" & 6 + i).FormulaR1C1 = "=if(row() = R15C3,R5C2,IF(OFFSET(" & iCodes(i, 1) & sheetNum & _
"!R2C1,,RC[3]-1) < R17C3,MATCH(R17C3,OFFSET(" & iCodes(i, 1) & sheetNum & "!A:A,,RC[3]-1),1)+1,2))"

to

.Range("H" & 6 + i).FormulaR1C1 = "=if(row() = R15C3,R5C2,IF(OFFSET(" & iCodes(i, 1) & sheetNum & _
"!R2C1,,RC[3]-1) < R17C3,MATCH(R17C3,OFFSET(" & iCodes(i, 1) & sheetNum & "!C1,,RC[3]-1),1)+1,2))"

Why should it make a difference if I use 'C1', or 'A:A'? Both are correct in my mind

 

[PHV]

Why should it make a difference if I use 'C1', or 'A:A'?
.Formula[!]R1C1[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jlt199]

I had originally used .value I've only switched to .FormulaR1C1 in the last few minutes.

I guess if you start with one convention then you have to stick to it and Excel doesn't like you mixing them up.

 

[PHV]

I had originally used .value
You'd use .Formula

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

[tt]
=OFFSET(GFDBG!A:A,,K8-1)

arg1: Anchor reference s/b GFDBG!$A$1
arg2: Row offset from anchor ref; you have ZERO
arg3: Column offset from anchor ref; you have K8-1 (what's in K8???
arg4: Rows of data to return
arg5: Columns of data to return
[/tt]
OFFSET returns a RANGE of data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}