Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA auto fill problems
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA auto fill problems
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 -- bdcris ----------------------------------------------------------------------- bdcrisp's Profile: http://www.excelforum.com/member.php...nfo&userid=356 View this thread: http://www.excelforum.com/showthread.php?threadid=26296 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA auto fill problems
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? "bdcrisp" wrote: 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 -- bdcrisp ------------------------------------------------------------------------ bdcrisp's Profile: http://www.excelforum.com/member.php...fo&userid=3564 View this thread: http://www.excelforum.com/showthread...hreadid=262963 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA auto fill problems
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. Heath wrote: 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? "bdcrisp" wrote: 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 -- bdcrisp ------------------------------------------------------------------------ bdcrisp's Profile: http://www.excelforum.com/member.php...fo&userid=3564 View this thread: http://www.excelforum.com/showthread...hreadid=262963 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-populate, Auto-copy or Auto-fill? | Excel Worksheet Functions | |||
Formula Fill Problems | Excel Discussion (Misc queries) | |||
Problems with auto-fill of dates | Excel Discussion (Misc queries) | |||
using auto fill edit or fill handel | Excel Worksheet Functions | |||
auto filter and auto fill | Excel Programming |