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 =
basebook.Worksheets("NetWeatherResidualLookup").Ra nge("A1:B10000")
SourceRcount = sourceRange.Rows.Count
Set destrange =
mybook.Worksheets("NetWeatherResidualLookup").Cell s(rnum, "A")

With sourceRange
Set destrange =
mybook.Worksheets("NetWeatherResidualLookup").Cell s(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()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

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

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

"A good example is twice the value of good advice."


"Need Help Fast!" wrote:

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 =
basebook.Worksheets("NetWeatherResidualLookup").Ra nge("A1:B10000")
SourceRcount = sourceRange.Rows.Count
Set destrange =
mybook.Worksheets("NetWeatherResidualLookup").Cell s(rnum, "A")

With sourceRange
Set destrange =
mybook.Worksheets("NetWeatherResidualLookup").Cell s(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()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub