A friendly Howdy to both of you, and welcome to vbaexpress to you somail :-) I am afraid I missed that you had just joined.

Just because part of this (the 'why ain't this string working in Evaluate?' part) was frustrating me for a moment, and because I cannot think of when I last used R1C1 notation (and thus, had not run into this issue), I did a small amount of testing. So... just in case it adds anything worthwhile:

Setup:

On worksheet named "MAIN", I entered values of {4,6,8,,,16,18,20,22} in B3:K3, where I left E3:G3 empty as indicated.

I then stepped thru this a couple of times:

[vba]
Sub test()
Dim lCountA As Long
Dim lSum As Long
On Error GoTo SimpleFix
lCountA = Evaluate("COUNTA(Main!B3:K3)")
lCountA = Evaluate("COUNTA(Main!R3C2:R3C11)")
lSum = Evaluate("SUM(Main!B3:K3)")
lSum = Evaluate("SUM(Main!R3C2:R3C11)")

lCountA = 0: lSum = 0

lCountA = IIf(Application.ReferenceStyle = xlA1, _
Evaluate("COUNTA(Main!B3:K3)"), _
Evaluate("COUNTA(Main!R3C2:R3C11)") _
)

lSum = IIf(Application.ReferenceStyle = xlA1, _
Evaluate("SUM(Main!B3:K3)"), _
Evaluate("SUM(Main!R3C2:R3C11)") _
)

Range("Main!E6").Value = lCountA
Range("Main!E7").Value = lSum

Exit Sub
SimpleFix:
lCountA = 0
lSum = 0
MsgBox Err.Number & vbCrLf & Err.Description
Err.Clear
Resume Next
End Sub
[/vba]

With A1 notation in effect:

The first lCountA = ... executes fine as one would expect, and likewise the second errors.
The first lSum = ... executes fine as one would expect, and again, the second errors.
The IIf Function gives proper returns of 7 and 94.

With R1C1 notation in effect:

The first lCountA = ... returns 1! The second lCountA correctly returns 7.
The first lSum = ... errors and the second correctly returns 94.
The IIf Function gives proper returns.

Well, not sure if that is worthwhile info, but it would seem to me that in a case where we think it advantageous to use Evaluate on a formula, we cannot depend upon an error occurring, and thus, could not depend upon error handling to fix anything. Since we cannot control what notation the user chooses, an IIf or If...Then would seem prudent.

FWIW, in the current scenario, I would certainly still just tack the formula into the cell and overwrite it with Range.Value = Range.Value.

Mark