Macro to copy formula to all rows that contain data in columns A:C

[jasonthedce]

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(INDIRECT("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.

 

[Ken Johnson]

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