VBA auto fill problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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. 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
 
Heath

Only adapted code where marked, (that is not amended the long-winded code
created by the macro recorder) It assigns a value to the new variable
lLastRow by finding the last row being used in column B and then uses this
to set the limit of the new destination range

Sub SP()
'
' SP Macro
' Macro recorded 9/22/2004 by hrh
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Dim lLastRow As Long 'entry here
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"
lLastRow = Range("B65536").End(xlUp).Row 'Entry here
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:C" & lLastRow) 'Entry here
Range("C2:C205").Select
Range("D2").Select
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
I am having difficulty making my macro adaptable to different sizes of
data.
Click to expand...

I don't quite understand the question, but would setting...

.FillAdjacentFormulas = True

help in any way?

HTH
Dana DeLouis


Heath said:
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. 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
Click to expand...

<snip>
 
Back
Top