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.
Bookmarks