I tried posting this over in a VBScripting group and they suggesting
coming here, instead. Some additional background:

I found the original macro posted by Tom Olgilvy, but it was posting a
simple sum formula. Substituting the more complex array formula below
broke the function. I was able to get the function to paste, but not
operate as an array formula. Any help would be much appreciated.

Jason

The original post:
I'm trying to make a command button on an Excel sheet that will add a
function to column D whenever there is data in columns A:C. So far, I
have the following:

Dim rng As Range
With Worksheets("Shop Log")
Set rng = .Range(.Cells(1, 3), .Cells(Rows.Count, 3).End(xlUp))
End With
rng.Offset(0, 1).Formula =
"{=MID(C2,MATCH(FALSE,ISERROR(1*MID(C2,ROW(INDIREC T("1:"&LEN(C2))),1)),0),LEN(C2)-SUM(1*ISERROR(1*MID(C2,ROW(INDIRECT("1:"&LEN(C2))) ,1))))*1}"
End Sub

The double quote in the INDIRECT functions causes a compile error,
Expected: end of statement. I assume this is because of the leading
quote after "Formula =", but I don't know how to fix this. Any help
would be much appreciated.

Hi Jason,

Apparently the rule is to double up the internal quotes, so for each of
the INDIRECTS change to ""1:"". That got rid of the "Expected end of
statement error".

Also, I think you don't include the "{" and "}" in the formula, I think
you use
"FormulaArray = "=MID(etc" instead of Formula = "{=Mid(etc"


Ken Johnson