Resullt from VBA formula and manual entry differnt

[ob3ron02]

Hi I've got a macro designed to fill cells with formulas when executed.
The results are sometimes incorrect but when writing the exact same
formula manually, the results are correct.

For example one of the formulas that I'm using is


Code:
--------------------
Dim ReportD As String
ReportD = "=IF(ISNA(MATCH(R[0]C[-2],PID!B:B,0))=FALSE, ""x"","""")"
Worksheets("Report").Range("D5").Offset(counter, 0).Formula = ReportD
--------------------


When this is executed the formula in D5 becomes
Code:

 

[Bernie Deitrick]

ob3ron02,

What happens if you force a calculation using this line after you put the
formula in through code?

Application.CalculateFull

HTH,
Bernie
MS Excel MVP

 

[Jim Rech]

The problem seems to be caused by your mixing of A1 and R1C1 formats in a
single formula. You have to go all the way with one or the other, e.g.,:

ReportD = "=IF(ISNA(MATCH(R[0]C[-2],PID!C[-2],0))=FALSE, ""x"","""")"

--
Jim Rech
Excel MVP
|
| Hi I've got a macro designed to fill cells with formulas when executed.
| The results are sometimes incorrect but when writing the exact same
| formula manually, the results are correct.
|
| For example one of the formulas that I'm using is
|
|
| Code:
| --------------------
| Dim ReportD As String
| ReportD = "=IF(ISNA(MATCH(R[0]C[-2],PID!B:B,0))=FALSE, ""x"","""")"
| Worksheets("Report").Range("D5").Offset(counter, 0).Formula = ReportD
| --------------------
|
|
| When this is executed the formula in D5 becomes
| Code:
| --------------------
| =IF(ISNA(MATCH(B5,PID!B:B,0))=FALSE, "x","")
| --------------------
| which returns an "x". Now if I type in the formula
| Code:
| --------------------
| =IF(ISNA(MATCH(B5,PID!B:B,0))=FALSE, "x","")
| --------------------
| in an adjacent cell, it returns the correct result, which is blank.
|
| What perhaps is causing this strange behaviour?
|
|
| --
| ob3ron02
| ------------------------------------------------------------------------
| ob3ron02's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=15450
| View this thread: http://www.excelforum.com/showthread.php?threadid=270451
|