Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AVERAGEIF FORMULA EXCEL 2007 | Excel Discussion (Misc queries) | |||
Is there an AVERAGEIF function in excel? | Excel Discussion (Misc queries) | |||
Help with Averageif Formula (don't think I should use Averageif) | Excel Discussion (Misc queries) | |||
Excel AverageIF issue... PLEASE HELP! | Excel Worksheet Functions | |||
Using formulas in conditions (SUMIF, AVERAGEIF) | New Users to Excel |