VBA auto fill problems

[Heath]

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,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

 

[bdcrisp]

Modify the bottom of your code

"=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:C"
ActiveSheet.UsedRange.Rows.Count)
End Sub

'This will fill to the maximum number of used rows on the sheet.

'Let me know how that works out for yo

 

[Heath]

Thank you very much. This did the trick. I have one question though. What
am I telling the program to do when I give it the "&
ActiveSheet.UsedRange.Rows.Count" command?

 

[Dave Peterson]

If you ever had anything in row 1, then ActiveSheet.UsedRange.Rows.Count will
give you the last row that's been used.

If you hit ctrl-end in xl, it'll be that row.

Thank you very much. This did the trick. I have one question though. What
am I telling the program to do when I give it the "&
ActiveSheet.UsedRange.Rows.Count" command?

Modify the bottom of your code

"=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:C" &
ActiveSheet.UsedRange.Rows.Count)
End Sub

'This will fill to the maximum number of used rows on the sheet.

'Let me know how that works out for you