CSE formulas in vba(ie: averageif in Excel 2003)



I have a cse equation in a worksheet. I am trying to paste the equation to
multiple workbooks in a folder. My problem is when I do this since I can't
hit cntrl-shift-enter it returns an error. Is there a way in vba when it says
to paste as a formula for it to know to paste it as a cse formula? Here is
the complete code. Thanks

Sub Macro8()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim SourceRcount As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim Unprotectworksheet As Worksheet

SaveDriveDir = CurDir
MyPath = "D:\Profiles\cherring\My Documents\AllStates\New Folder"
ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1

Do While FNames <> ""
If FNames <> basebook.Name Then
Set mybook = Workbooks.Open(FNames)
Set sourceRange =
SourceRcount = sourceRange.Rows.Count
Set destrange =
mybook.Worksheets("NetWeatherResidualLookup").Cells(rnum, "A")

With sourceRange
Set destrange =
mybook.Worksheets("NetWeatherResidualLookup").Cells(rnum, "A"). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Formula = sourceRange.Formula

Application.DisplayAlerts = False

mybook.Close ([True])
rnum = rnum
End If
FNames = Dir()
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub


CSE formulas are officially named "Array" formulas. Try
YourRange.FormulaArray = SomeArrayFormula

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question