I am having difficulty making my macro adaptable to different sizes of data.
This is an expansion to my moving average formula. The macro works for a set
amount of data but does not perform correctly when the amount of data is
changed. The data is pulled from the desktop (which is the begining of the
code) and then the operation is performed. The specific problem is the auto
fill of my function I don't how else to fill it to the end of the data. If
anyone has suggestions as to how I could make this macro work for different
sizes of data I would appreciate the suggestion.

Sub SP()
'
' SP Macro
' Macro recorded 9/22/2004 by hrh
'
' Keyboard Shortcut: Ctrl+Shift+S
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\arthur\hrh$\Desktop\table.csv", Destination:=Range("A1"))
.Name = "table"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Selection.End(xlDown).Select
Selection.ClearContents
Range("A1").Select
ActiveWindow.SmallScroll Down:=-3
Columns("A:A").EntireColumn.AutoFit
Range("B:B,C:C,D:D,F:F,G:G").Select
Range("G1").Activate
Selection.ClearContents
Columns("E:E").Select
Selection.Cut Destination:=Columns("B:B")
Range("C1").Select
ActiveCell.FormulaR1C1 = "SMA"
Range("D1").Select
ActiveCell.FormulaR1C1 = "1"
Range("C2").Select
ActiveCell.FormulaR1C1 = _

"=IF(COUNTIF(R1C2:R[-1]C[-1],""0"")=R1C4,AVERAGE(OFFSET(R[-1]C[-1],0,0,-R1C4)),"""")"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C205")
Range("C2:C205").Select
Range("D2").Select
End Sub






Manage Your Profile
©2004 Microsoft Corporation. All rights reserved. Terms of Use |Trademarks
|Privacy Statement